[BUGS] BUG #2303: UPDATE from manual is incorrect
The following bug has been logged online: Bug reference: 2303 Logged by: Massimo Fidanza Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Linux Description:UPDATE from manual is incorrect Details: Hi all, I have an update similar to the one included in Postgresql documentation in Postgresql 8.1 -> VI. Reference -> I. SQL Commands -> UPDATE The query is the last but one UPDATE employees SET last_closed_deal = deal.id FROM accounts JOIN deals ON (account.id = deal.account_id) WHERE deal.employee_id = employees.id AND deal.name = 'Rocket Powered Skates' AND accounts.name = 'Acme Corporation' ORDER BY deal.signed_date DESC LIMIT 1; this query is not correct and doesn't work with postgresql 8.1. My query that doesn't work is the first and I modify it in second form that is the correct one. - FIRST QUERY (NOT WORKING) --- update tblstock set npezzi = sum(sr.npezzi) ,npezzirimanenti = sum(sr.npezzi) ,modelloid = sr.modelloid ,objid = sr2.objid ,tipostockid = 3 from tblstockrighe sr join tblstockrighe sr2 on sr.modelloid = sr2.modelloid and sr2.id = 110 where sr.stockid = tblstock.id and sr.stockid = 270 group by sr.stockid ,sr.modelloid ,sr2.objid - SECOND QUERY (WORK) update tblstock set npezzi = sr.npezzi ,npezzirimanenti = sr.npezzi ,modelloid = sr.modelloid ,objid = sr.objid ,tipostockid = 3 from (select sr.stockid ,sum(sr.npezzi) as npezzi ,sr.modelloid ,sr2.objid from tblstockrighe sr join tblstockrighe sr2 on sr.modelloid = sr2.modelloid and sr2.id = 110 where sr.stockid = 270 group by sr.stockid ,sr.modelloid ,sr2.objid ) sr where sr.stockid = tblstock.id ---(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
[BUGS] BUG #2305: "No bufferspace available" error on large insert
The following bug has been logged online: Bug reference: 2305 Logged by: H. Guijt Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Windows 2000 Description:"No bufferspace available" error on large insert Details: We are using PostgreSQL 8.0.3 on Windows 2000, and using pgsql to insert a series of records into a database. The table into which we are inserting has a string column, two integer columns, and two BYTEA columns. Upon issuing the insert statement I receive the following error message: "could not send data to server: No buffer space available (0x2747/10055)" I suspect that this happens because we are inserting a lot of data: the first BYTEA column is about 1.8MB, while the second is about 0.5MB. However... - The same operation completes without problems when running on other machines (which are all running Windows XP and the same Postgres version). - We have inserted even longer records (10MB+) on this machine without problems. - This insert is part of a larger group of inserts; the total group size is the largest we have ever tried on this machine. - The problem can be reliably reproduced. - The machine has 384MB of memory. It gives no indication of being starved for memory during the operation (responsiveness of the system is not compromised). ---(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
[BUGS] BUG #2308: pg_dump -a does not respect referential dependencies
The following bug has been logged online: Bug reference: 2308 Logged by: Matthew George Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Darwin Kernel Version 8.5.0: Sun Jan 22 10:38:46 PST 2006; root:xnu-792.6.61.obj~1/RELEASE_PPC Power Macintosh powerpc Description:pg_dump -a does not respect referential dependencies Details: When using pg_dump to dump a database, the schema and data are arranged within the dump so that it may be imported without violating referential integrity. When using the -a option to get a data-only dump, the data is ordered in the dump alphabetically by table. If a new schema is loaded into a fresh database, the output from the data-only dump cannot be imported via \i in psql without manually editing the dump file and reordering the inserts / copies such that dependent tables have their data loaded before the tables that depend on them. This is inconvenient at best. Since the logic obviously exists within pg_dump already to arrange the data in the correct order of reference dependencies, can this be added to the code path for `pg_dump -a` as well? Or can another option be added that specifies alphabetic vs. dependency order? This would really cut down the time it takes to do schema upgrades on projects that I work with. Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2306: Duplicate primary key
The following bug has been logged online:
Bug reference: 2306
Logged by: Andreas Jung
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.9
Operating system: Linux
Description:Duplicate primary key
Details:
Ihave the following table (with 'id' as primary key:
Toolbox2Test=# \d hierarchy
Table "public.hierarchy"
Column |Type |
Modifiers
-+-+
-+-+
---
id | integer | not null default
nextval('public.hierarchy_id_seq'::text)
parentid| bigint |
idprodukt | bigint |
bezeichnung | character varying(160) |
neudat | timestamp without time zone |
aedat | timestamp without time zone |
benutzer| character varying(32) |
pos | integer | default 0
linkindex | character varying(20) |
deleted | boolean | default false
visible | boolean |
sorting | boolean |
comment | character varying(265) |
idhierarchy_share | integer |
show_gattung_in_bauplan | boolean | default false
sortierung | character varying(10) |
Indexes:
"hierarchy_pkey" PRIMARY KEY, btree (id)
"hierarchy_deleted_idx" btree (deleted)
"hierarchy_idhierarchy_share_idx" btree (idhierarchy_share)
"hierarchy_idprodukt_idx" btree (idprodukt)
This gives me two rows with the same id=5077:
Toolbox2Test=# select * from hierarchy where id >= 5077 order by id;
id | parentid | idprodukt | bezeichnung
| neudat | aedat
| benutzer | pos | linkindex | deleted | visible | sorting |
comment | idhierarchy_share |
show_gattung_in_bauplan
| sortierung
---+--+---+-
--++
---
-+--+-+---+-+-+-+---
-+---+--
---
+
5077 | 4062 | | Präsentieren
|| 2005-11-23
12:03:38.617
969 | RossmyU | 1 | LI353323 | f | | | CSV
import from test_tools.csv | | f
|
5077 | 4062 | | Präsentation
|| 2005-11-24
15:43:50.756
414 | RossmyU | 0 | LI353323 | t | | | CSV
import from test_tools.csv | | t
|
5078 | 4062 | | Rechner
|| 2005-11-23
12:03:38.61
7969 | RossmyU | 2 | LI353324 | f
Search for all rows with id=5077 returns this:
Toolbox2Test=# select * from hierarchy where id = 5077;
id | parentid | idprodukt | bezeichnung | neudat | aedat
| benutzer | pos | linkindex | deleted | visible | sorting |
comment | idhierarchy_share | show_gattung_in_bauplan |
sortierung
--+--+---+--++--
--+--+-+---+-+-+-+--
--+---+-+---
-
5077 | 4062 | | Präsentieren || 2005-11-23
12:03:38.617969 | RossmyU | 1 | LI353323 | f | | |
CSV import from test_tools.csv | | f
|
(1 row)
Any idea how to resolve this issue?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #2307: Buckup and sequences in DEFAULT part
The following bug has been logged online:
Bug reference: 2307
Logged by: Giza Kamil
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: Windows,Linux
Description:Buckup and sequences in DEFAULT part
Details:
Hi!
My script is:
CREATE SEQUENCE schema.seq_taid;
CREATE TABLE schema.tab1(ID integer DEFAULT nextval('schema.seq_taid'));
After runing on server I have:
CREATE TABLE schema.tab1(ID integer DEFAULT
nextval('schema.seq_taid'::regclass));
but on 8.0 version was
CREATE TABLE schema.tab1(ID integer DEFAULT
nextval(('schema.seq_taid'::text)::regclass));
Buckups on 8.0 were OK, but on 8.1 I'm getting samething like that:
CREATE TABLE schema.tab1(ID integer DEFAULT nextval('seq_taid'::regclass));
Buckup cat schema name from sequence name in function nextval in DEFAULT
part.
Problem exists in buckups in PgAdmin and pg_buckup from command line on
server.
Is there any kind of solution for that or I have to wait for next version.
It's a very important for me to resolve this problem as quickly as
possible.
Best regards,
Kamil Giza
---(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] BUG #2304: Time zone bug: '05:04 GMT+3' processing as '05:04:00 -03'
The following bug has been logged online: Bug reference: 2304 Logged by: Alexander Pivovarov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: FreeBSD 6.1 Description:Time zone bug: '05:04 GMT+3' processing as '05:04:00 -03' Details: # select '05:04 GMT+3'::time with time zone; timetz - 05:04:00-03 (1 row) ---(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 #2306: Duplicate primary key
On Tue, Mar 07, 2006 at 04:43:18PM +, Andreas Jung wrote: > PostgreSQL version: 7.4.9 7.4.12 is the latest in that branch; it contains several bug fixes since 7.4.9. > This gives me two rows with the same id=5077: > > Toolbox2Test=# select * from hierarchy where id >= 5077 order by id; What's the output of the following command? SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077 ORDER BY id; > Search for all rows with id=5077 returns this: > > Toolbox2Test=# select * from hierarchy where id = 5077; [...] > (1 row) Does the same query return different results depending on whether you use an index scan or a sequential scan? What do you get for these queries? SET enable_seqscan TO on; SET enable_indexscan TO off; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; SET enable_seqscan TO off; SET enable_indexscan TO on; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; -- Michael Fuhr ---(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 #2305: "No bufferspace available" error on large insert
... Upon issuing the insert statement I receive the following error message: "could not send data to server: No buffer space available (0x2747/10055)" I suspect that this happens because we are inserting a lot of data: the first BYTEA column is about 1.8MB, while the second is about 0.5MB. However... - The same operation completes without problems when running on other machines (which are all running Windows XP and the same Postgres version). ... are the various tuning parameters in $PGDATA/postgresql.conf set the same on these various systems ? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2308: pg_dump -a does not respect referential dependencies
"Matthew George" <[EMAIL PROTECTED]> writes: > Since the logic obviously exists within pg_dump already to arrange the data > in the correct order of reference dependencies, can this be added to the > code path for `pg_dump -a` as well? No. In a data-only restore there may not be *any* ordering that works --- consider circular dependencies. The best practice is to do standard schema+data dumps, wherein the ordering problem can be handled properly by not creating the FK constraints until after the data is loaded. If you really want to do a data-only restore, I'd suggest dropping the FK constraints and re-adding them afterwards (which will be a lot faster than row-by-row retail checks would be, anyway). Another possibility is the --disable-triggers option, but I can't really recommend that, because if there are any referential problems in the data you load, that way will fail to catch it. regards, tom lane ---(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 #2307: Buckup and sequences in DEFAULT part
"Giza Kamil" <[EMAIL PROTECTED]> writes:
> Buckups on 8.0 were OK, but on 8.1 I'm getting samething like that:
> CREATE TABLE schema.tab1(ID integer DEFAULT nextval('seq_taid'::regclass));
You haven't said why you think this is a problem. The dump is perfectly
valid AFAICS.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2304: Time zone bug: '05:04 GMT+3' processing as '05:04:00 -03'
"Alexander Pivovarov" <[EMAIL PROTECTED]> writes: > # select '05:04 GMT+3'::time with time zone; >timetz > - > 05:04:00-03 > (1 row) This isn't a bug; it's expected. The "GMT+n" syntax is defined by a different standard than the one we follow for timestamp output, and the two standards don't agree about sign. It's unfortunate but there's not a lot we can do about it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2308: pg_dump -a does not respect referential dependencies
On Mar 8, 2006, at 3:01 PM, Tom Lane wrote: "Matthew George" <[EMAIL PROTECTED]> writes: The best practice is to do standard schema+data dumps, wherein the ordering problem can be handled properly by not creating the FK constraints until after the data is loaded. If you really want to do a data-only restore, I'd suggest dropping the FK constraints and re-adding them afterwards (which will be a lot faster than row-by-row retail checks would be, anyway). BTW, it would be really nice if we provided a better way to do this than manually dropping all the FK constraints and adding them back in later. Would it be difficult to allow deferring all constraints in the database during a specified transaction? That would allow for loading the data in a transaction and doing the constraint checking later... -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2308: pg_dump -a does not respect referential dependencies
Jim Nasby <[EMAIL PROTECTED]> writes: > BTW, it would be really nice if we provided a better way to do this > than manually dropping all the FK constraints and adding them back in > later. Would it be difficult to allow deferring all constraints in > the database during a specified transaction? That would allow for > loading the data in a transaction and doing the constraint checking > later... You can try SET CONSTRAINTS ALL DEFERRED, but that only works for constraints that are declared deferrable, which by default FK constraints are not (stupid but that's what the spec requires). In any case this would still have performance issues because the behavior is tuned for transactions that update relatively small numbers of rows. Drop/add constraint is a lot better choice in the context of a bulk load. I was toying just now with the idea of a pg_dump mode that would issue the drop and re-add constraint commands for you. This would only help for constraints that pg_dump knows of (ie were in the source database), not any random new FK constraints that might be in the DB you are loading into, but it'd sure beat doing it manually. regards, tom lane ---(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 #2303: UPDATE from manual is incorrect
"Massimo Fidanza" <[EMAIL PROTECTED]> writes: > The query is the last but one > UPDATE employees SET last_closed_deal = deal.id > FROM accounts JOIN deals ON (account.id = deal.account_id) > WHERE deal.employee_id = employees.id > AND deal.name = 'Rocket Powered Skates' > AND accounts.name = 'Acme Corporation' > ORDER BY deal.signed_date DESC LIMIT 1; > this query is not correct and doesn't work with postgresql 8.1. It still works, if you enable add_missing_from. But I agree that the example shouldn't assume that. > My query that doesn't work is the first and I modify it in second form that > is the correct one. That appears to be an entirely unrelated issue, which is whether aggregates in the SET list of an UPDATE make any sense. I'm inclined to think not --- what are you aggregating over? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2303: UPDATE from manual is incorrect
I wrote: > "Massimo Fidanza" <[EMAIL PROTECTED]> writes: >> this query is not correct and doesn't work with postgresql 8.1. > It still works, if you enable add_missing_from. I take that back --- FROM is not the issue (indeed, it has one). The problem is the ORDER BY and LIMIT clauses. I dunno who put in this example, but I'd like some of what they were smoking. I've taken it out again, since without that it's not really showing anything that the prior examples don't cover. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
