Обсуждение: 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