Re: [ADMIN] [SQL] Postgres schema comparison.
Jim Buttafuoco mentioned : => I use dblink to attach to both databases and query pg_namespace, pg_class, pg_attribute ... to get the diffs. See => attached as an example. look for the dblink_connect lines to specify your database. You will need to install => contrib/dblink. I used this with 7.4.X series and have NOT tested yet with 8.0.X. Thanks! This is something I haven't even thought of. Only some of the machines have dblink installed at the moment, but that's the same work as having to install pgcrypto everywhere. This is actually more thorough. It seems to be working with some minor changes on 7.3 (The dblink functions don't allow multiple connections, and take only one argument, so I created temp tables in stead). This is actually very fast. Thanks again. Kind Regards Stefan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Query issue/8.0.1/Serendipity
I have an 8.0.1 server running the Blogging software serendipity, and the following query fails with "relation e not defined", but it is on the first line: "SELECT timestamp FROM serendipity_entries e, serendipity_category c, serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4" The software is at: http://www.s9y.org/ here is the exact response from Pg: serendipity=# SELECT timestamp FROM serendipity_entries e, serendipity_categoryc, serendipity-# serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties serendipity-# ep_cache_extended ON (e.id = ep_cache_extended.entryid AND serendipity(# ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity-# serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid serendipity(# AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity-# serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND serendipity(# ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties serendipity-# ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = serendipity(# 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 serendipity-# AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property serendipity(# IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR serendipity(# (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND serendipity-# c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4; ERROR: relation "e" does not exist serendipity=# What is wrong? serendipity=# select version(); version - PostgreSQL 8.0.1 on i686-unknown-sysv5UnixWare7.1.4, compiled by cc (1 row) serendipity=# -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query issue/8.0.1/Serendipity
Larry Rosenman wrote: I have an 8.0.1 server running the Blogging software serendipity, and the following query fails with "relation e not defined", but it is on the first line: "SELECT timestamp FROM serendipity_entries e, serendipity_category c, serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties I think this line here is the problem. You've told it to LEFT OUTER JOIN from "ec", not "e". ep_cache_extended ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4" -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 05:48 am, Larry Rosenman wrote: > I have an 8.0.1 server running the Blogging software serendipity, and the > following query fails with "relation e not defined", but it is on the first > line: [snip] I can put up a pg_dump -s if that would help any LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query issue/8.0.1/Serendipity
Larry, Restating your SQL in a more reader-friendly form: SELECT timestamp FROM serendipity_entries e , serendipity_category c , serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended') LEFT OUTER JOIN serendipity_entryproperties ep_cache_body ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body') LEFT OUTER JOIN serendipity_entryproperties ep_access ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_entryproperties ep_sticky ON (e.id = ep_sticky.entryid AND ep_sticky.property = 'ep_is_sticky') WHERE e.timestamp >= 1109656800 AND e.timestamp <= 1112335200 AND e.timestamp <= 1110241185 AND e.isdraft = 'false' AND ( ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1) ) AND e.id = ec.entryid AND c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4 I can see you're using a lot of left joins. You are beginning your left joins off of ec, so I believe neither e nor c can be referenced as join conditions. It might work if you reorder the first part of the FROM clause as the following: FROM serendipity_category c , serendipity_entrycat ec , serendipity_entries e LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended (Though I can't be sure without seeing table definitions.) It also appears you have a redundant e.timestamp constraint in the WHERE clause: if e.timestamp is >= 1110241185 it's definitely going to be <= 1112335200 Hope this helps. I find white space helps me read my own SQL much more easily. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: > Larry, > > Restating your SQL in a more reader-friendly form: [snip] > This is from an error page (and machine generated to boot :) ) > I can see you're using a lot of left joins. You are beginning your left > joins off of ec, so I believe neither e nor c can be referenced as join > conditions. It might work if you reorder the first part of the FROM > clause as the following: > > FROM serendipity_category c > , serendipity_entrycat ec > , serendipity_entries e > LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended > > (Thugh I can't be sure without seeing table definitions.) a pg_dump -s is at: http://www.lerctr.org/~ler/s9y.sql > > It also appears you have a redundant e.timestamp constraint in the > WHERE clause: if e.timestamp is >= 1110241185 it's definitely going to > be <= 1112335200 this query is machine generated, so I'm sure it's based on timestamps. > > Hope this helps. I find white space helps me read my own SQL much more > easily. understood, as I said, it's machine generated :) > > Michael Glaesemann > grzm myrealbox com -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 06:17 am, Richard Huxton wrote: > Larry Rosenman wrote: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the > > first line: > > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > I think this line here is the problem. You've told it to LEFT OUTER JOIN > from "ec", not "e". > and re-arranging the FROM clause to put serendipity_entries e right before the LEFT OUTER JOIN runs. I've reported this to the s9y folks. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query issue/8.0.1/Serendipity
On Mar 8, 2005, at 21:28, Larry Rosenman wrote: On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: Larry, Restating your SQL in a more reader-friendly form: [snip] This is from an error page (and machine generated to boot :) ) Regardless if it's machine-generated or not, I'd still reformat it so I could read it more easily, and definitely before sending it to a mailing list to ask for advice. But that's me. I can see you're using a lot of left joins. You are beginning your left joins off of ec, so I believe neither e nor c can be referenced as join conditions. It might work if you reorder the first part of the FROM clause as the following: FROM serendipity_category c , serendipity_entrycat ec , serendipity_entries e LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended So, did this work? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 06:35 am, Michael Glaesemann wrote: > On Mar 8, 2005, at 21:28, Larry Rosenman wrote: > > On Tuesday 08 March 2005 06:21 am, Michael Glaesemann wrote: > >> Larry, > >> > >> Restating your SQL in a more reader-friendly form: > > > > [snip] > > > > This is from an error page (and machine generated to boot :) ) > > Regardless if it's machine-generated or not, I'd still reformat it so I > could read it more easily, and definitely before sending it to a > mailing list to ask for advice. But that's me. > > >> I can see you're using a lot of left joins. You are beginning your > >> left > >> joins off of ec, so I believe neither e nor c can be referenced as > >> join > >> conditions. It might work if you reorder the first part of the FROM > >> clause as the following: > >> > >> FROM serendipity_category c > >> , serendipity_entrycat ec > >> , serendipity_entriess e > >> LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended > > So, did this work? Yes, as I stated in another post. I've reported it to the Serendipity folks (as I'm not sure where the query is coming from. Thanks (to all, btw). LER > > Michael Glaesemann > grzm myrealbox com -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query issue/8.0.1/Serendipity
Larry Rosenman writes: > I have an 8.0.1 server running the Blogging software serendipity, and the > following query fails with "relation e not defined", but it is on the first > line: > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > ep_cache_extended.property = 'ep_cache_extended') ... Broken SQL that's only ever been tested on MySQL. Last I heard, MySQL treated this sort of construct as joining left-to-right, ie, FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... in which case the left argument of the LEFT JOIN already contains e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately for MySQL users everywhere, this is expressly contrary to the SQL spec: per spec, JOIN binds more tightly than commas in the FROM-list do. (Is this on the mysql gotchas page?) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 09:37 am, Tom Lane wrote: > Larry Rosenman writes: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the > > first line: > > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > > ep_cache_extended.property = 'ep_cache_extended') ... > > Broken SQL that's only ever been tested on MySQL. > > Last I heard, MySQL treated this sort of construct as joining > left-to-right, ie, > > FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... > > in which case the left argument of the LEFT JOIN already contains > e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately > for MySQL users everywhere, this is expressly contrary to the SQL spec: > per spec, JOIN binds more tightly than commas in the FROM-list do. Thanks, Tom. Garvin Hicking (the Serendipity Developer) confirms it works on MySQL, and I wasn't sure about the spec. Thanks for confirming it's a MySQL gotcha :) LER > > (Is this on the mysql gotchas page?) > >regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Query issue/8.0.1/Serendipity
>On Tue, 08 Mar 2005 10:37:51 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Larry Rosenman writes: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the first > > line: > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > > ep_cache_extended.property = 'ep_cache_extended') ... > > Broken SQL that's only ever been tested on MySQL. > > Last I heard, MySQL treated this sort of construct as joining > left-to-right, ie, > > FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... > > in which case the left argument of the LEFT JOIN already contains > e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately > for MySQL users everywhere, this is expressly contrary to the SQL spec: > per spec, JOIN binds more tightly than commas in the FROM-list do. > > (Is this on the mysql gotchas page?) Nope, although as my plans for the week involve evaluating Serendipity using PostgreSQL I'll look into it. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query issue/8.0.1/Serendipity
On Tuesday 08 March 2005 10:55 am, Ian Barwick wrote: > > (Is this on the mysql gotchas page?) > > Nope, although as my plans for the week involve evaluating > Serendipity using PostgreSQL I'll look into it. Garvin already made a patch to CVS to fix this in s9y. See the php-blog-devs list. LER > > Ian Barwick > [EMAIL PROTECTED] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] datestyle setting
hi, i have a website with a postgresql backend. Being a shared site, i have no root access. i do have shell access to the database, but cannot create or drop the database. I access the database through python scripts using the psycopg adapter. My question is, how do i set the pgdatestyle in the database as 'European,sql'? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net àà à! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] datestyle setting
Kenneth Gonsalves <[EMAIL PROTECTED]> writes: > i have a website with a postgresql backend. Being a shared site, i > have no root access. i do have shell access to the database, but > cannot create or drop the database. I access the database through > python scripts using the psycopg adapter. My question is, how do i > set the pgdatestyle in the database as 'European,sql'? If it's a reasonably recent version of PG, either ALTER DATABASE SET or ALTER USER SET might serve. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] datestyle setting
On Wednesday 09 Mar 2005 11:57 am, Tom Lane wrote: > Kenneth Gonsalves <[EMAIL PROTECTED]> writes: > > i have a website with a postgresql backend. Being a shared site, > > i have no root access. i do have shell access to the database, > > but cannot create or drop the database. I access the database > > through python scripts using the psycopg adapter. My question is, > > how do i set the pgdatestyle in the database as 'European,sql'? > > If it's a reasonably recent version of PG, either ALTER DATABASE > SET or ALTER USER SET might serve. postgresql 7.4. when i type: alter database set datestyle to 'European' i get 'syntax error at or near "datestyle" at character 20' -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net àà à! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
