[BUGS] psql \i command with a missing file name doesn't rollback the transaction
Hi, We've discovered a surprising behavior of psql \i command. What we sometimes to add new tables to the database is: begin; \i /path/to/table/definitions/table1.sql \i /path/to/table/definitions/table2.sql ... \i /path/to/table/definitions/tableN.sql commit; What we discovered that some files in the /path/to/table/definitions were missing (say, table 2,3), but the table 1, 4... N appeared in the database after executing the transaction. This is quite a catch, since we cannot rely on transaction consistency when using an include directive. The test is simple: begin; \i whatever; select 1; commit; The expected behavior was that select 1 would lead to 'ERROR: current transaction is aborted'. The current behavior is that it is executed, although a message is emitted to a client: whatever: No such file or directory Would it be possible from the client side to generate the rollback to the server on an attempt to include a non-existing file (perhaps only when ON_ERROR_STOP is set to 1?). -- Regards, Alexey Klyukin
Re: [BUGS] Known issues for PostgreSQL server 8.1.19
Hi Andres, Thanks for your reply. We can't upgrade to newer version as of now. We just want to know list of known issues on 8.1.19. We will just note them down and do some impact analysis. Regards, Anurag Dashputre -Original Message- From: Andres Freund [mailto:and...@2ndquadrant.com] Sent: Wednesday, September 18, 2013 8:28 PM To: Dashputre, Anurag (GE Healthcare) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Known issues for PostgreSQL server 8.1.19 Hi, On 2013-09-18 11:56:36 +, Dashputre, Anurag (GE Healthcare) wrote: > We are using PostgreSQL server 8.1.19 in our product and as part of SDLC > activities, we would like to know about the Known Issues present in this > version. The primary issue - especially regarding lifecycle - is that 8.1 is out of support and thus unmaintained for more than 2 years. Check http://www.postgresql.org/support/versioning/ for details. The also have been several maintenance releases with important fixes in the 8.1 line since then. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Known issues for PostgreSQL server 8.1.19
Dashputre, Anurag (GE Healthcare) wrote on 20.09.2013 08:39: Thanks for your reply. We can't upgrade to newer version as of now. We just want to know list of known issues on 8.1.19. We will just note them down and do some impact analysis. You will need to go through the release notes for every version after 8.1.19 and see what was fixed - those should be the "open issues" in your release. An upgrade to a new minor version (8.1.19 to 8.1.23) should never be a problem though. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Known issues for PostgreSQL server 8.1.19
On 9/20/2013 10:05 AM, Thomas Kellerer wrote: Dashputre, Anurag (GE Healthcare) wrote on 20.09.2013 08:39: Thanks for your reply. We can't upgrade to newer version as of now. We just want to know list of known issues on 8.1.19. We will just note them down and do some impact analysis. You will need to go through the release notes for every version after 8.1.19 and see what was fixed - those should be the "open issues" in your release. An upgrade to a new minor version (8.1.19 to 8.1.23) should never be a problem though. basically, start at E.100 here, http://www.postgresql.org/docs/9.3/static/release.html and read every release note newer than that. 8.1.19 was released in December 2009, about the same time as 8.2.15, 8.3.9, and 8.4.2, so when you did your code freeze on that version, there were already 3 newer major releases. the final 8.1 release, 8.1.23, was released in 2010-12-16 concurrently with 8.2.19, 8.3.13, 8.4.16, and 9.0.2. bug fixes after that date were not applied to 8.1, so you should probably also read all the release notes from 9.0.3 to 9.0.13, filtering them for features that were added after 8.1 (for instance, replication related bugs obviously aren't applicable to 8.1) I really really don't like to see that phrase, "we can't upgrade to newer version". do you also run 5 year old operating systems that are unsupported and unpatched?sadly, this is all too common in the embedded world, where no planning consideration is given towards product maintenance and updating. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
1.) During our prior upgrade process we used pg_upgrade to move from pg 8.4.3 to 9.1.6 using the hard links install option, we also have our data spread across disk storage mediums; fiber, nas. Are there any known issues, bugs with using pg_upgrade to move from 9.1.6 to pg 9.3?2.) We also have to stand up a fresh install of pg 9.3 with postgis 2.1 and restore the data from a 9.1.6 dump. Are there any known issues, bugs with using pg_dump or pg_dumpall and then pg_restore to get this done?thanks
Re: [BUGS] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
On 9/20/2013 1:51 PM, fburg...@radiantblue.com wrote: 1.) During our prior upgrade process we used pg_upgrade to move from pg 8.4.3 to 9.1.6 using the hard links install option, we also have our data spread across disk storage mediums; fiber, nas. Are there any known issues, bugs with using pg_upgrade to move from 9.1.6 to pg 9.3? having multiple tablespaces on different mount points complicates things. I'm not sure how or if pg_upgrade handles that. -- john r pierce 37N 122W somewhere on the middle of the left coast
[BUGS] BUG #8464: Update with subquery with limit occasionally updates too many rows
The following bug has been logged on the website:
Bug reference: 8464
Logged by: Joel Turkel
Email address: jtur...@salsify.com
PostgreSQL version: 9.2.4
Operating system: Ubunutu 12.04 (64-bit)/Kernel version 3.2
Description:
An update statement in my application that uses a limit subquery to select a
single primary key value for a row to update occasionally updates multiple
rows. We've only noticed this problem in our test automation runs and it
only occurs roughly every 25-50 test runs with no discernible pattern for
when it occurs. Attempts to isolate the problem into a consistently
reproducible test case have been unsuccessful. The schema for the relevant
tables in question are:
CREATE TABLE delayed_jobs
(
id serial NOT NULL,
priority integer DEFAULT 0,
attempts integer DEFAULT 0,
handler text,
last_error text,
run_at timestamp without time zone,
locked_at timestamp without time zone,
failed_at timestamp without time zone,
locked_by character varying(255),
queue character varying(255),
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
job_group_id integer,
blocked boolean NOT NULL DEFAULT false,
CONSTRAINT delayed_jobs_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE INDEX delayed_jobs_priority
ON delayed_jobs
USING btree
(priority, run_at)
WHERE failed_at IS NULL AND blocked = false;
CREATE INDEX index_delayed_jobs_on_job_group_id
ON delayed_jobs
USING btree
(job_group_id);
CREATE TABLE delayed_job_groups
(
id serial NOT NULL,
on_completion_job text,
on_completion_job_options text,
queueing_complete boolean NOT NULL DEFAULT false,
blocked boolean NOT NULL DEFAULT false,
CONSTRAINT delayed_job_groups_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Here's a log of the SQL statements run during a recent test failure (there
were hundreds of tests run before this so including all statements run since
startup would be a bit unwieldy):
SAVEPOINT active_record_1
INSERT INTO "delayed_job_groups" ("blocked", "on_completion_job",
"on_completion_job_options", "queueing_complete") VALUES ($1, $2, $3, $4)
RETURNING "id" [["blocked", false], ["on_completion_job", "---
!ruby/object:CompletionJob {}\n"], ["on_completion_job_options", nil],
["queueing_complete", false]]
RELEASE SAVEPOINT active_record_1
SAVEPOINT active_record_1
INSERT INTO "delayed_jobs" ("attempts", "blocked", "created_at",
"failed_at", "handler", "job_group_id", "last_error", "locked_at",
"locked_by", "priority", "queue", "run_at", "updated_at") VALUES ($1, $2,
$3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id"
[["attempts", 0], ["blocked", false], ["created_at", Fri, 20 Sep 2013
00:08:47 UTC +00:00], ["failed_at", nil], ["handler", "---
!ruby/object:FailingJob {}\n"], ["job_group_id", 920], ["last_error", nil],
["locked_at", nil], ["locked_by", nil], ["priority", 0], ["queue", nil],
["run_at", Fri, 20 Sep 2013 00:08:47 UTC +00:00], ["updated_at", Fri, 20 Sep
2013 00:08:47 UTC +00:00]]
-- Inserts row with 254
RELEASE SAVEPOINT active_record_1
SAVEPOINT active_record_1
INSERT INTO "delayed_jobs" ("attempts", "blocked", "created_at",
"failed_at", "handler", "job_group_id", "last_error", "locked_at",
"locked_by", "priority", "queue", "run_at", "updated_at") VALUES ($1, $2,
$3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id" [0m
[["attempts", 0], ["blocked", false], ["created_at", Fri, 20 Sep 2013
00:08:47 UTC +00:00], ["failed_at", nil], ["handler", "---
!ruby/object:NoOpJob {}\n"], ["job_group_id", 920], ["last_error", nil],
["locked_at", nil], ["locked_by", nil], ["priority", 0], ["queue", nil],
["run_at", Fri, 20 Sep 2013 00:08:47 UTC +00:00], ["updated_at", Fri, 20 Sep
2013 00:08:47 UTC +00:00]]
-- Inserts row with id 254
RELEASE SAVEPOINT active_record_1
SAVEPOINT active_record_1
SELECT "delayed_job_groups".* FROM "delayed_job_groups" WHERE
"delayed_job_groups"."id" = $1 LIMIT 1 FOR UPDATE [["id", 920]]
UPDATE "delayed_job_groups" SET "queueing_complete" = 't' WHERE
"delayed_job_groups"."id" = 920
SELECT 1 AS one FROM "delayed_jobs" WHERE "delayed_jobs"."job_group_id" IN
(920) AND "delayed_jobs"."failed_at" IS NULL LIMIT 1
RELEASE SAVEPOINT active_record_1
SELECT COUNT(*) FROM "delayed_jobs" WHERE "delayed_jobs"."failed_at" IS
NULL
SELECT COUNT(*) FROM "delayed_job_groups"
UPDATE "delayed_jobs" SET locked_at = '2013-09-20 00:08:47.540592',
locked_by = 'host:box239 pid:11983' WHERE id IN (SELECT id FROM
"delayed_jobs" WHERE "delayed_jobs"."blocked" = 'f' AND ((run_at <=
'2013-09-20 00:08:47.540120' AND (locked_at IS NULL OR locked_at <
'2013-09-19 20:08:47.540135') OR locked_by = 'host:box239 pid:11983') AND
failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE)
RETURNING *
-- Updates both rows 253 and 254 but the limit should result in only one row
being updated
There is no concurrent access to the database during the test in question. I
r
