Re: [SQL] obtaining column names from tables or views
John van Zantvoort wrote: moin, I've got a question. I'm scripting against an existing postgresql database environment and need to tweak queries based on the columns in the views or tables, how can if find these w/o querying the tables or views. There is a whole section of the manual devoted to the system catalogues: http://www.postgresql.org/docs/8.2/static/catalogs.html Or there's the "information schema" which is in a standardised layout but doesn't include PG-specific details. http://www.postgresql.org/docs/8.2/static/information-schema.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] obtaining column names from tables or views
moin, I've got a question. I'm scripting against an existing postgresql database environment and need to tweak queries based on the columns in the views or tables, how can if find these w/o querying the tables or views. Currently I execute the querie and collect the columns but this is rather expensive, silly and fails when the table or view is empty. -- Met vriendelijke groeten/with kind regards John van Zantvoort --- Most burning issues generate far more heat than light. -BEGIN GEEK CODE BLOCK- Version: 3.12 GCM d-- s:+ a C++ UL P+++ L++ E--- W++ N+ o-- K- w O M- V- PS+ PE- Y PGP++ t 5 X R- tv- b+ DI+ D+ G e h--- r++ y++ --END GEEK CODE BLOCK--
Re: [SQL] NULLIF problem
On Nov 27, 2007, at 8:47 PM, Michael Glaesemann wrote: On Nov 27, 2007, at 21:04 , Gera Mel Handumon wrote: I encounter an error if i use NULLIF with timestamp with time zone. eq. dbtime=nullif(mytime,'') i want to null the value of field DBTIME if the variable mytime=" " DBTIME ="timestamp with time zone" datatype error: column DBTIME is of type timestamp with time zone but expression is of type text. I believe the reason is that '' is not a valid timestamp value: think of it this way: IF mytime = '' THEN mytime := NULL; END IF; The first thing it needs to do is compare the mytime value with ''. As '' is not a valid timestamp value, it may be casing mytime to text. You'll run into problems if you're assigning a text value to a timestamp field (which happens after the initial comparison--and the cast--are done.) I think you may need to handle this is you middleware, or handle the IF THEN explicitly in a function. Maybe CASE would work: CASE WHEN mytime = '' THEN NULL ELSE CAST(mytime AS TIMESTAMP) END Why not just: UPDATE table SET mytime=NULL WHERE mytime=''; Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mardi, novembre 27, 2007 23:46 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR > > UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n - > > 1). I'm trying to find whether this is an identified issue with > > PostgreSQL 8.1 that might have been fixed in a later version such as > > 8.2; I don't have any problem in moving to a later version if needed. > > There's no known issue specifically of that form (and a quick test of > 8.1 doesn't reproduce any such behavior). However, it is known and > documented that LIMIT and FOR UPDATE behave rather oddly together: > the LIMIT is applied first, which means that if FOR UPDATE rejects > any rows as being no longer up-to-date, you get fewer than the expected > number of rows out. You did not mention any concurrent activity in > your example, but I'm betting there was some ... > > regards, tom lane Yes, you were betting right. However I would have thought that the SELECT ... FOR UPDATE statement blocks if another process were locking the same rows. The record values don't change from a call to another. I did read the documentation, especially the section that Bruce Momjian's pointed me out, but I don't think that it corresponds to this case (cf. my test). I did the following test, removing all the where-clause from the SELECT statement. Every statement completes immediately, i.e. it doesn't block. agoratokens=> select id from "Tokens" id - 47 104 44 42 33 69 94 89 90 ... Time: 119.314 ms agoratokens=> select id from "Tokens" limit 2 for update; id - 47 104 (2 rows) Time: 17.679 ms agoratokens=> select id from "Tokens" limit 3 for update; id - 47 104 (2 rows) Time: 20.452 ms The statement doesn't return the row where id equals to 44. agoratokens=> select id from "Tokens" limit 3; id - 47 104 44 (3 rows) Time: 1.186 ms The statement returns the row where id equals to 44. agoratokens=> select id from "Tokens" limit 3 for update; id - 47 104 (2 rows) Time: 9.473 ms The statement still doesn't return the row where id equals to 44. agoratokens=> select id from "Tokens" where id IN (47, 104, 44, 42) limit 3 for update; id - 47 104 44 (3 rows) This time, the statement returns the row where id equals to 44. agoratokens=> select id from "Tokens" limit 3; id - 47 104 44 (3 rows) Time: 7.547 ms agoratokens=> select id from "Tokens" limit 5 for update; id - 47 104 33 (3 rows) Time: 11.725 ms This time, the statement doesn't return the rows where id equals to 44 and 42. agoratokens=> select id from "Tokens" limit 8 for update; id - 47 104 33 69 94 89 (6 rows) Time: 11.794 ms The statement still doesn't return the rows where id equals to 44 a 42. agoratokens=> select id from "Tokens" where id = 44 limit 3 for update; id 44 (1 row) Time: 14.172 ms The statement does return the row where id equals to 44. "However, it is known and documented that LIMIT and FOR UPDATE behave rather oddly together: the LIMIT is applied first, which means that if FOR UPDATE rejects any rows as being no longer up-to-date, you get fewer than the expected number of rows out." Tom, when you say "rows as being no longer up-to-date", do you mean which values don't match anymore the where-clauses of the SELECT statement? If so, that doesn't correspond to my test since I remove every where-clause. Any ideas, any other tests I can try? Thanks, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
"Daniel Caune" <[EMAIL PROTECTED]> writes: > I did the following test, removing all the where-clause from the SELECT > statement. Every statement completes immediately, i.e. it doesn't block. I think you left out some critical information, like who else was doing what to the table. What it looks like to me is that the third and fourth rows in this view were live according to your transaction snapshot, but were committed dead as of current time, and so FOR UPDATE wouldn't return them. > agoratokens=> select id from "Tokens" where id IN (47, 104, 44, 42) limit 3 > for update; > This time, the statement returns the row where id equals to 44. No, it returns *some* row where id equals 44. Not necessarily the same one seen in the seqscan. (I imagine this query is using an index, and so would visit rows in a different physical order.) Printing the ctid of the rows would confirm or disprove that theory. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
> De : Tom Lane [mailto:[EMAIL PROTECTED]
>
> "Daniel Caune" <[EMAIL PROTECTED]> writes:
> > I did the following test, removing all the where-clause from the SELECT
> statement. Every statement completes immediately, i.e. it doesn't block.
>
> I think you left out some critical information, like who else was doing
> what to the table.
>
> What it looks like to me is that the third and fourth rows in this view
> were live according to your transaction snapshot, but were committed
> dead as of current time, and so FOR UPDATE wouldn't return them.
>
> > agoratokens=> select id from "Tokens" where id IN (47, 104, 44, 42)
> limit 3 for update;
> > This time, the statement returns the row where id equals to 44.
>
> No, it returns *some* row where id equals 44. Not necessarily the same
> one seen in the seqscan. (I imagine this query is using an index, and
> so would visit rows in a different physical order.) Printing the ctid
> of the rows would confirm or disprove that theory.
>
> regards, tom lane
Thanks Tom. I think this time you will point me out the problem. The column
id has a primary key constraint on. There should not be more than one row with
id equals to 44.
agoratokens=> \d "Tokens"
Table "public.Tokens"
Column | Type | Modifiers
---++---
id| integer| not null default
nextval('"Tokens_id_seq"'::regclass)
type | integer| not null
value | character varying(255) | not null
isLocked | boolean| not null default false
timestamp | timestamp(6) without time zone |
Indexes:
"Tokens_pkey" PRIMARY KEY, btree (id)
(...)
agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3 for
update;
ctid | id | type | value | isLocked | timestamp
--++--+---+--+---
(0 rows)
agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens=> select count(*) from "Tokens" where id = 44;
count
---
1
(1 row)
It seems that, in certain condition, row (199,84) is shadowing row (3702,85);
my feeling from a "customer" high level. Indeed, as a PostgreSQL core
developer, that assertion could make you laugh... :-)
I took into account your point about the concurrent context. Therefore I
isolated the database from any connection except mine.
# TYPE DATABASE USER IP-ADDRESS IP-MASKMETHOD
local all all trust
hostall all 127.0.0.1 255.255.255.0 password
#hostall all 10.3.41.0 255.255.254.0 password
sudo /etc/init.d/postgresql-8.1 restart
* Restarting PostgreSQL 8.1 database server [ ok ]
No other client than my psql was connected to PostgreSQL. You can trust me.
The result is exactly the same:
agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens=> select ctid, * from "Tokens" where "isLocked" = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens=> select ctid, * from "Tokens" where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens=> select count(*) from "Tokens" where id = 44;
count
---
1
(1 row)
By the way, according to the "business logic", the timestamp "2007-04-03
12:12:02.
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
"Daniel Caune" <[EMAIL PROTECTED]> writes: > It seems that, in certain condition, row (199,84) is shadowing row > (3702,85); This would be the expected behavior if row (199,84) were an updated version of row (3702,85), but you couldn't see it yet in your current transaction snapshot. A plain SELECT would show the older version (the current one according to the snapshot) while SELECT FOR UPDATE would show the newest committed version. I think you must have somehow got a corrupt-data situation with respect to the commit status of these rows, but it's not real clear how. Would you show us the xmin and xmax of the rows, and also the current transaction counter? (pg_controldata will give you a close-enough idea of the latter.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
Tom Lane wrote: > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > It seems that, in certain condition, row (199,84) is shadowing row > > (3702,85); > > This would be the expected behavior if row (199,84) were an updated > version of row (3702,85), but you couldn't see it yet in your current > transaction snapshot. A plain SELECT would show the older version > (the current one according to the snapshot) while SELECT FOR UPDATE > would show the newest committed version. Hmm. We've been studying a case on one customer where xmin/xmax seem to be corrupted. It has had ups and downs because I have my doubts about their storage system, but I'm not completely sure that it can be really blamed. This is on 8.1.10. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" Major Fambrough: You wish to see the frontier? John Dunbar: Yes sir, before it's gone. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
