Обсуждение: pg_constraint missing many entries?
Almost all of my foreign key definitions are NOT present in pg_constraint. However, they do exist as triggers. It appears that *new* foreign keys, whether defined in new tables or added on via "ALTER TABLE" commands, make it into pg_constraint, but the "old" stuff that was present from before our 7.3 upgrade, exists only as triggers. 1) Is this a bug? 2) Is there any way to "refresh" pg_constraint? (I'm experimenting with a tool that graphs a schema, and it needs a bit of code written to determine from a table and column name whether that column is a foreign key, and to what table. pg_constraint where contype = 'f' seems to be the ticket, but on my database it's inadequate.) -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
That's right, pg_constraint didn't exist before 7.3. In the contrib directory there is a script called adddepend which attempts to create the missing records for you. On Mon, Nov 17, 2003 at 08:20:54PM +0000, Jeff Boes wrote: > Almost all of my foreign key definitions are NOT present in > pg_constraint. However, they do exist as triggers. It appears that *new* > foreign keys, whether defined in new tables or added on via "ALTER > TABLE" commands, make it into pg_constraint, but the "old" stuff that > was present from before our 7.3 upgrade, exists only as triggers. > > 1) Is this a bug? > > 2) Is there any way to "refresh" pg_constraint? (I'm experimenting with > a tool that graphs a schema, and it needs a bit of code written to > determine from a table and column name whether that column is a foreign > key, and to what table. pg_constraint where contype = 'f' seems to be > the ticket, but on my database it's inadequate.) > > > -- > Jeff Boes vox 269.226.9550 ext 24 > Database Engineer fax 269.349.9076 > Nexcerpt, Inc. http://www.nexcerpt.com > ...Nexcerpt... Extend your Expertise > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Вложения
Hi all, I know we have LIMIT to limit the number of rows returned, I wonder if there is a way to indicate an offset. e.g. Select * from Test offset 10, limit 4; retrieve all Test, return 4 rows from the 10th row thanks, kathy
Kathy Zhu wrote: > I know we have LIMIT to limit the number of rows returned, I wonder if there is > a way to indicate an offset. > Select * from Test offset 10, limit 4; As per the PostgreSQL documentation, specifically the page on the "SELECT" SQL command: LIMIT Clause LIMIT { count | ALL } OFFSET start where count specifies the maximum number of rows to return, and start specifies the number of rows to skip before starting to return rows. so the query you want is: SELECT * FROM Test LIMIT 4 OFFSET 10; Alex
Martijn van Oosterhout wrote: > That's right, pg_constraint didn't exist before 7.3. In the contrib > directory there is a script called adddepend which attempts to create the > missing records for you. > And much to my dismay, it has a bug (or a serious limitation). Table (before): ... Indexes: ix_foo_1 unique(fn_myfunc(foo_column)) becomes Table (after): ... Indexes: ix_foo_1 unique(foo_column)) That is, the script turned a functional index into an index on a column. Bad, bad news. It's fortunate I did this on our test system; it's unfortunate that I did it at the end of the day. Overnight, we ran a few HUNDRED THOUSAND queries against a large table using no index ... -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
On Mon, 17 Nov 2003 16:17:20 -0700, Kathy Zhu wrote: > I know we have LIMIT to limit the number of rows returned, I wonder if > there is a way to indicate an offset. > > e.g. > > Select * from Test offset 10, limit 4; Make that SELECT * FROM Test LIMIT 4 OFFSET 10; > retrieve all Test, return 4 rows from the 10th row Almost. In PostgreSQL, OFFSET x means skip the first x rows. See also http://www.postgresql.org/docs/7.4/static/sql-select.html#SQL-LIMIT -- Greetings from Troels Arvin, Copenhagen, Denmark