[BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
The following bug has been logged online: Bug reference: 3403 Logged by: Jasen Betts Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: window XP (vmware) Description:ver 8.2 can't add serial column to temp table,but 8.1 can Details: gymmaster=# select version(); version -- PostgreSQL 8.2.0 on i686-pc-mingw32, compiled by GCC cc.exe (GCC) 3.4.2 (mingw-special) (1 row) template1=# create temp table foo ( x text); CREATE TABLE template1=# alter table foo add column y text ; ALTER TABLE template1=# alter table foo add column id serial; NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for serial colum n "foo.id" ERROR: relation "public.foo" does not exist template1=# this worked in version 8.1.8 (linux) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Jasen Betts wrote: The following bug has been logged online: Bug reference: 3403 Logged by: Jasen Betts Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: window XP (vmware) Description:ver 8.2 can't add serial column to temp table,but 8.1 can Details: gymmaster=# select version(); version -- PostgreSQL 8.2.0 on i686-pc-mingw32, compiled by GCC cc.exe (GCC) 3.4.2 (mingw-special) (1 row) template1=# create temp table foo ( x text); CREATE TABLE template1=# alter table foo add column y text ; ALTER TABLE template1=# alter table foo add column id serial; NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for serial colum n "foo.id" ERROR: relation "public.foo" does not exist template1=# It does not work on 8.2.4 as well. It seems PG lost information about schema and try to use default schema. Following command works well: alter table pg_temp.foo add column id serial; It could be use as workaround. Zdenek ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3401: PITR does not work in the case ofrecovery_target_xid = 'SELECT_only_transaction_ID'
On Thu, 2007-06-21 at 11:17 +, Katsuhiko Okano wrote: > Description:PITR does not work in the case of recovery_target_xid = > 'SELECT_only_transaction_ID' > Details: If a transaction is purely read only then there is no COMMIT or ABORT message written to the transaction log. As a result there is no way to know at what point that transaction occurred, nor any way to use the recovery_target_xid on those kinds of transactions. This is not a bug in PITR, it is a specific optimisation of WAL to improve the throughput of read-only queries. I'll add this to the docs in my next round of docs changes. How did you come to choose an xid of this nature? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Zdenek Kotala wrote:
Jasen Betts wrote:
template1=# create temp table foo ( x text);
CREATE TABLE
template1=# alter table foo add column y text ;
ALTER TABLE
template1=# alter table foo add column id serial;
NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for
serial
colum
n "foo.id"
ERROR: relation "public.foo" does not exist
template1=#
It does not work on 8.2.4 as well. It seems PG lost information about
schema and try to use default schema. Following command works well:
alter table pg_temp.foo add column id serial;
It could be use as workaround.
8.1 creates the sequence in wrong schema:
postgres=# create temp table foo ( x text);
CREATE TABLE
postgres=# alter table foo add column id serial;
NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
ALTER TABLE
postgres=# \d
List of relations
Schema |Name| Type | Owner
---++--+--
pg_temp_1 | foo| table| hlinnaka
public| foo_id_seq | sequence | hlinnaka
(2 rows)
The problem seems to be in transformColumnDefinition, where the schema
of the to-be-created sequence is determined from the relation name
given. The default creation schema is used, if the user didn't specify
the schame of the table explicitly, but since it's an ALTER TABLE, it
really should use the schema of the existing table.
Patch against 8.2 attached, seems to apply to 8.1 and CVS head though I
haven't tested them.. This is not my area of expertise, so I'm not 100%
sure this is the right way to fix it.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/parser/analyze.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.353
diff -c -r1.353 analyze.c
*** src/backend/parser/analyze.c 11 Oct 2006 16:42:59 - 1.353
--- src/backend/parser/analyze.c 22 Jun 2007 10:16:20 -
***
*** 69,74
--- 69,75
{
const char *stmtType; /* "CREATE TABLE" or "ALTER TABLE" */
RangeVar *relation; /* relation to create */
+ Oid namespace; /* namespace of relation to alter */
List *inhRelations; /* relations to inherit from */
bool hasoids; /* does relation have an OID column? */
bool isalter; /* true if altering existing table */
***
*** 945,950
--- 946,952
cxt.stmtType = "CREATE TABLE";
cxt.relation = stmt->relation;
+ cxt.namespace = InvalidOid;
cxt.inhRelations = stmt->inhRelations;
cxt.isalter = false;
cxt.columns = NIL;
***
*** 1087,1093
* quite unlikely to be a problem, especially since few people would
* need two serial columns in one table.
*/
! snamespaceid = RangeVarGetCreationNamespace(cxt->relation);
snamespace = get_namespace_name(snamespaceid);
sname = ChooseRelationName(cxt->relation->relname,
column->colname,
--- 1089,1098
* quite unlikely to be a problem, especially since few people would
* need two serial columns in one table.
*/
! if (OidIsValid(cxt->namespace))
! snamespaceid = cxt->namespace;
! else
! snamespaceid = RangeVarGetCreationNamespace(cxt->relation);
snamespace = get_namespace_name(snamespaceid);
sname = ChooseRelationName(cxt->relation->relname,
column->colname,
***
*** 3010,3015
--- 3015,3021
List *newcmds = NIL;
bool skipValidation = true;
AlterTableCmd *newcmd;
+ Relation relation;
cxt.stmtType = "ALTER TABLE";
cxt.relation = stmt->relation;
***
*** 3024,3029
--- 3030,3045
cxt.alist = NIL;
cxt.pkey = NULL;
+ relation = heap_openrv(stmt->relation, AccessShareLock);
+ if (relation->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation \"%s\" is not a table",
+ stmt->relation->relname)));
+
+ cxt.namespace = RelationGetNamespace(relation);
+
+
/*
* The only subtypes that currently require parse transformation handling
* are ADD COLUMN and ADD CONSTRAINT. These largely re-use code from
***
*** 3166,3171
--- 3182,3194
*extras_before = list_concat(*extras_before, cxt.blist);
*extras_after = list_concat(cxt.alist, *extras_after);
+ /*
+ * Close the parent rel, but keep our AccessShareLock on it until xact
+ * commit. That will prevent someone else from deleting or ALTERing the
+ * table before we get to execute the changes.
+ */
+ heap_close(relation, NoLock);
+
return qry;
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #3401: PITR does not work in the case ofrecovery_target_xid = 'SELECT_only_transaction_ID'
Simon Riggs wrote: On Thu, 2007-06-21 at 11:17 +, Katsuhiko Okano wrote: Description:PITR does not work in the case of recovery_target_xid = 'SELECT_only_transaction_ID' Details: (snip) How did you come to choose an xid of this nature? specify log_statement = 'all' and log_line_prefix = '[%x]' in postgresql.conf (I know this approach is not useful and hardly used on actual system management.) output server log like below: [621]LOG: statement: CREATE TABLE xxx(col1 integer); [622]LOG: statement: SELECT * FROM xxx; [623]LOG: statement: SELECT * FROM xxx; [624]LOG: statement: SELECT * FROM xxx; [625]LOG: statement: INSERT INTO xxx VALUES (1); [626]LOG: statement: INSERT INTO xxx VALUES (2); [627]LOG: statement: INSERT INTO xxx VALUES (3); [628]LOG: statement: SELECT * FROM xxx; [629]LOG: statement: SELECT * FROM xxx; [630]LOG: statement: SELECT * FROM xxx; Regards, -- Katsuhiko Okano okano katsuhiko _at_ oss ntt co jp ---(end of broadcast)--- TIP 1: 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: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Heikki Linnakangas wrote: Zdenek Kotala wrote: Jasen Betts wrote: template1=# create temp table foo ( x text); CREATE TABLE template1=# alter table foo add column y text ; ALTER TABLE template1=# alter table foo add column id serial; NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for serial colum n "foo.id" ERROR: relation "public.foo" does not exist template1=# It does not work on 8.2.4 as well. It seems PG lost information about schema and try to use default schema. Following command works well: alter table pg_temp.foo add column id serial; It could be use as workaround. 8.1 creates the sequence in wrong schema: postgres=# create temp table foo ( x text); CREATE TABLE postgres=# alter table foo add column id serial; NOTICE: ALTER TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" ALTER TABLE postgres=# \d List of relations Schema |Name| Type | Owner ---++--+-- pg_temp_1 | foo| table| hlinnaka public| foo_id_seq | sequence | hlinnaka (2 rows) The problem seems to be in transformColumnDefinition, where the schema of the to-be-created sequence is determined from the relation name given. The default creation schema is used, if the user didn't specify the schame of the table explicitly, but since it's an ALTER TABLE, it really should use the schema of the existing table. Correct. Patch against 8.2 attached, seems to apply to 8.1 and CVS head though I haven't tested them.. This is not my area of expertise, so I'm not 100% sure this is the right way to fix it. I looked on it, but I think let parser to fill namespace information in ctx->relation structure should be better then do it in this place. There is also unfilled istemp flag. Zdenek ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Temp table woes
This only works as a superuser, but it's wrong nevertheless: Session 1: postgres=# CREATE temp table foo (x int); CREATE TABLE Session 2: postgres=# SELECT * FROM pg_temp_1.foo; x --- (0 rows) postgres=# INSERT INTO pg_temp_1.foo values (1); INSERT 0 1 The insert in session 2 goes to a buffer in the shared buffer cache, which causes problems at the next checkpoint: ERROR: could not open relation 1663/11502/24576: No such file or directory CONTEXT: writing block 0 of relation 1663/11502/24576 WARNING: could not write block 0 of 1663/11502/24576 DETAIL: Multiple failures --- write error might be permanent. Without superuser rights, you'll get a permission denied error trying to access the table from another session. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Error message that is a bit misleading / weird result from || null
Hi, > As it seems, the result is actually a correct array It's an array of xid, indeed. > And comparing the result to for example an int, works. Only equality only, that is. > Sidenote: since it does solve my problem, I can now build an index > based on such an interpretation of xmin I was too hasty with that one, it obviously still won't work because of the lack of operators like '<', '>', etc. So using xidsend() for that still seems to be the only 'valid' trick. -- Best, Frank. ---(end of broadcast)--- TIP 1: 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
[BUGS] Error message that is a bit misleading / weird result from || null
Hi,
While trying to find a way to get indexed access to a table based on it's
xmin, I ran into the following error message that seems a bit misleading:
db=# select xmin || ' ' from limit 1;
ERROR: array value must start with "{" or dimension information
Toying a bit more with this result resulted in:
megafox=# select xmin || null from limit 1;
?column?
{12115328}
(1 row)
As it seems, the result is actually a correct array:
db=# select (xmin || null)[1] from limit 1;
?column?
--
12115328
(1 row)
And comparing the result to for example an int, works.
Now I understand that there are no standard casts of xid to something else,
but the interpretation if this concatenation surprised at least me ;)
Sidenote: since it does solve my problem, I can now build an index based on
such an interpretation of xmin, I'm a bit anxious as to how this will
be 'fixed', if at all ;)
db=# select version();
version
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3
(1 row)
--
Best,
Frank.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Zdenek Kotala wrote: I looked on it, but I think let parser to fill namespace information in ctx->relation structure should be better then do it in this place. There is also unfilled istemp flag. Ignore this. It is good place. However, I think add following function into namespace.c should be nicer solution. Oid RelnameGetSchemaid(const char *relname); See RelnameGetRelid. You can use snamespaceid = RelnameGetSchemaid(cxt->relation->relname); instead of snamespaceid = RangeVarGetCreationNamespace(cxt->relation); Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Error message that is a bit misleading / weird result from || null
"Frank van Vugt" <[EMAIL PROTECTED]> writes: > Sidenote: since it does solve my problem, I can now build an index based on > such an interpretation of xmin, I'm a bit anxious as to how this will > be 'fixed', if at all ;) Actually this was noticed only recently but that was precisely because it was related to some significant changes that were being made. Because of those changes 8.3 behaves markedly different in this area: postgres=# select xmin || 'x' from w limit 1; ?column? -- 1679x (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Temp table woes
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > This only works as a superuser, but it's wrong nevertheless: > Session 1: > postgres=# CREATE temp table foo (x int); > CREATE TABLE > Session 2: > postgres=# INSERT INTO pg_temp_1.foo values (1); > INSERT 0 1 I can't get excited about preventing that --- it looks to me like a superuser deliberately trying to break the system, and there are plenty of other ways he can do that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Error message that is a bit misleading / weird result from || null
Frank van Vugt <[EMAIL PROTECTED]> writes:
> While trying to find a way to get indexed access to a table based on it's
> xmin, I ran into the following error message that seems a bit misleading:
> db=# select xmin || ' ' from limit 1;
> ERROR: array value must start with "{" or dimension information
Yeah, it's being captured by the "anyelement || anyarray" operator for
lack of any other possible interpretation. We found a reasonable fix
for 8.3, in connection with removing implicit casts to text.
See long-running thread beginning here
http://archives.postgresql.org/pgsql-hackers/2007-02/msg01729.php
final solution invented beginning here
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00116.php
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3401: PITR does not work in the case ofrecovery_target_xid = 'SELECT_only_transaction_ID'
Katsuhiko Okano <[EMAIL PROTECTED]> writes: > Simon Riggs wrote: >> How did you come to choose an xid of this nature? > specify log_statement = 'all' and log_line_prefix = '[%x]' in postgresql.conf > (I know this approach is not useful and hardly used on actual system > management.) No, it's not very useful because you can't be sure that the order of commit records in the WAL file will match what you see in the postmaster log. If the transactions are sufficiently well spread apart in time that you *can* be sure of that, you might as well use timestamps anyway. The reason for having the XID option in recovery.conf at all is to allow an exact stop point specification when a timestamp is too inaccurate --- but in a situation like that, you'd really have to have grovelled through the WAL file with some kind of dump tool to determine which XID you want to specify. BTW, as of 8.3 commit timestamps have full gettimeofday() precision, they're not just time_t values; so the use-case for stopping by XID is even narrower than it used to be. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > 8.1 creates the sequence in wrong schema: Yeah, 8.0 and 8.1 both do the wrong thing really, so it's hard to argue that this ever worked. > The problem seems to be in transformColumnDefinition, where the schema > of the to-be-created sequence is determined from the relation name > given. The default creation schema is used, if the user didn't specify > the schame of the table explicitly, but since it's an ALTER TABLE, it > really should use the schema of the existing table. This is actually a bit nasty. Your proposed patch doesn't really work, because of the concern that is now commented at the head of transformAlterTableStmt: * CAUTION: resist the temptation to do any work here that depends on the * current state of the table. Actual execution of the command might not * occur till some future transaction. Hence, we do only purely syntactic * transformations here, comparable to the processing of CREATE TABLE. IOW, we don't actually *know* at parse analysis time which table will be affected. It's arguable that CREATE TABLE with a serial is broken too, because conceivably search_path could change between parsing and execution of the command, leading to the table being created in the new default schema while the sequence still goes into the old one. It looks to me like a "proper" fix requires postponing the formation of the CREATE SEQUENCE command until execution time, when we can know with some confidence what schema the table is in. Yech. That'll be pretty invasive ... is it worth the trouble? A possible alternative is to interpret CREATE/ALTER TABLE as nailing down the target schema at parse analysis time, ie, after analysis the query always looks as if you had written an explicit schema name rather than leaving it up to search_path. But this would be a behavioral change that would likely bite somebody; and it would be inconsistent with the behavior of other utility commands. Maybe we should give up doing any CREATE/ALTER processing at all at parse analysis time, and push it all to execution time. I got rid of parse-time processing of other utility statements during the plan caching work a couple months ago, because of concerns very much like this, but I hadn't bit the bullet for CREATE/ALTER TABLE because it was such a huge chunk of code. But maybe we'd better do it. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3404: Mix ORDER BY ASC With DESC
The following bug has been logged online: Bug reference: 3404 Logged by: Maxx Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Debian GNU/Linux 4.0 (Etch) Description:Mix ORDER BY ASC With DESC Details: When I mix the ORDER BY clause with ASC and DESC the return is just the first field, for example: "SELECT * FROM test ORDER BY id DESC, textbody ASC" return the fields only sorted by id desc. But, if I use ORDER BY id DESC, textbody DESC then its works. Thank you for your attention ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Tom Lane wrote: This is actually a bit nasty. Your proposed patch doesn't really work, because of the concern that is now commented at the head of transformAlterTableStmt: * CAUTION: resist the temptation to do any work here that depends on the * current state of the table. Actual execution of the command might not * occur till some future transaction. Hence, we do only purely syntactic * transformations here, comparable to the processing of CREATE TABLE. IOW, we don't actually *know* at parse analysis time which table will be affected. I don't understand that. Why would the execution be delayed to a future transaction? You can't PREPARE an ALTER TABLE, right? According to the comments in transformInhRelation, it has the same problem... Maybe we should give up doing any CREATE/ALTER processing at all at parse analysis time, and push it all to execution time. I got rid of parse-time processing of other utility statements during the plan caching work a couple months ago, because of concerns very much like this, but I hadn't bit the bullet for CREATE/ALTER TABLE because it was such a huge chunk of code. But maybe we'd better do it. We'll still need something smaller to back patch, I think. :( -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> IOW, we don't actually *know* at parse analysis time which table will be >> affected. > I don't understand that. Why would the execution be delayed to a future > transaction? You can't PREPARE an ALTER TABLE, right? Yeah, you can. Consider plpgsql, or protocol-level Bind. The fact that we don't expose these facilities as SQL doesn't mean they're not there. A cached statement in plpgsql is actually the main case I'm worried about... >> Maybe we should give up doing any CREATE/ALTER processing at all at >> parse analysis time, and push it all to execution time. > We'll still need something smaller to back patch, I think. :( At this point I don't think we'll try to fix this in the back branches. It's never really worked, so I don't see 8.2's behavior as a regression, and I don't see a small fix that doesn't create issues of its own. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3404: Mix ORDER BY ASC With DESC
"Maxx" <[EMAIL PROTECTED]> writes: > When I mix the ORDER BY clause with ASC and DESC the return is just the > first field, for example: You're going to have to provide a complete test case if you want anyone to take that seriously. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
