pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh
10+ messages / 7 participants
[nested] [flat]
* BUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh
@ 2010-01-14 02:01 James Bellinger <jfb@zer7.com>
2010-01-14 16:11 ` Re: BUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: James Bellinger @ 2010-01-14 02:01 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged online:
Bug reference: 5276
Logged by: James Bellinger
Email address: jfb@zer7.com
PostgreSQL version: 8.4.2
Operating system: Ubuntu 9.10
Description: pg_ctl reads data directory on -D instead of
postgresql.conf directoryh
Details:
While on startup, pg_ctl (correctly) looks in the directory specified by -D
for postgresql.conf, and then uses that to determine the data directory,
when stopping it actually expects the directories to be the same and in
doing so looks for the PID file in the given directory. As a result, on
start one needs to use the .conf directory with -D, and on stop the data
directory.
It's not a big deal, but for consistency's sake it ought to read the .conf
file to get the data directory on stop.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh
2010-01-14 02:01 BUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh James Bellinger <jfb@zer7.com>
@ 2010-01-14 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2010-01-14 16:11 UTC (permalink / raw)
To: James Bellinger <jfb@zer7.com>; +Cc: pgsql-bugs
"James Bellinger" <jfb@zer7.com> writes:
> While on startup, pg_ctl (correctly) looks in the directory specified by -D
> for postgresql.conf, and then uses that to determine the data directory,
> when stopping it actually expects the directories to be the same and in
> doing so looks for the PID file in the given directory. As a result, on
> start one needs to use the .conf directory with -D, and on stop the data
> directory.
Your premise is wrong --- pg_ctl does not read postgresql.conf at any
time. It's not intended to be used in a scenario where the
data_directory configuration option is being used.
There have been previous discussions about trying to teach it to read
the config file, but the effort needed seems far out of proportion to
the benefits :-(
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* BUG #18002: Duplicate entries of row possible even after having primary key
@ 2023-06-27 11:03 PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 10:28 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key David Rowley <dgrowleyml@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: PG Bug reporting form @ 2023-06-27 11:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ajinkya.tankhiwale@tcs.com
The following bug has been logged on the website:
Bug reference: 18002
Logged by: Ajinkya Tankhiwale
Email address: ajinkya.tankhiwale@tcs.com
PostgreSQL version: 14.7
Operating system: RHEL 8.8
Description:
We migrated from 13.7 to 14.7 recently, in current data setup, we say that
duplicate records are present in table even after primary key. I see that in
the past, same issue was reported and possible solution was to run full
vacuum or re-index. We tried both, but still able to enter duplicate
records.
This is blocker for us and would like to have solution for this as soon as
possible.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
@ 2023-06-27 20:36 ` Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Andres Freund @ 2023-06-27 20:36 UTC (permalink / raw)
To: ajinkya.tankhiwale@tcs.com; pgsql-bugs@lists.postgresql.org
Hi,
On 2023-06-27 11:03:16 +0000, PG Bug reporting form wrote:
> Bug reference: 18002
> Logged by: Ajinkya Tankhiwale
> Email address: ajinkya.tankhiwale@tcs.com
> PostgreSQL version: 14.7
> Operating system: RHEL 8.8
> Description:
>
> We migrated from 13.7 to 14.7 recently, in current data setup, we say that
> duplicate records are present in table even after primary key. I see that in
> the past, same issue was reported and possible solution was to run full
> vacuum or re-index. We tried both, but still able to enter duplicate
> records.
>
> This is blocker for us and would like to have solution for this as soon as
> possible.
You haven't provided information that'd allow us to debug the issue. At the
very least we'd need table + index definition and the inserts that are allowed
to happen despite being duplicates. Even better would be a way to reproduce
the issue.
You said that you migrated from 13.7 to 14.7 recently - did you also change
your OS version?
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
@ 2023-06-28 08:38 ` Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-06-28 20:59 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 10+ messages in thread
From: Ajinkya Tankhiwale @ 2023-06-28 08:38 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello,
Please find below DDL for one of the table
CREATE SEQUENCE IF NOT EXISTS seq_ao_id
CYCLE
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 999999999999999999
CACHE 1;
CREATE TABLE IF NOT EXISTS offer
(
action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
action_type character varying COLLATE pg_catalog."default" NOT NULL,
buy_sell_indicator character varying COLLATE pg_catalog."default" NOT NULL,
creation_date_time timestamp without time zone NOT NULL,
modification_date_time timestamp without time zone NOT NULL,
CONSTRAINT pk_ado PRIMARY KEY (action_id)
);
CREATE INDEX IF NOT EXISTS idx_ado_active_action_id
ON offer USING btree
(action_id ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_ao_modify_create_date_time
ON offer USING btree
(modification_date_time DESC NULLS FIRST, creation_date_time DESC NULLS FIRST)
TABLESPACE pg_default;
Interesting point here is, if I create new table then constraint violation is thrown, on old table I could enter duplicate records.
Also, we migrated from el7 to el8.
Regards,
Ajinkya
-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Tuesday, June 27, 2023 10:37 PM
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18002: Duplicate entries of row possible even after having primary key
"External email. Open with Caution"
Hi,
On 2023-06-27 11:03:16 +0000, PG Bug reporting form wrote:
> Bug reference: 18002
> Logged by: Ajinkya Tankhiwale
> Email address: ajinkya.tankhiwale@tcs.com
> PostgreSQL version: 14.7
> Operating system: RHEL 8.8
> Description:
>
> We migrated from 13.7 to 14.7 recently, in current data setup, we say
> that duplicate records are present in table even after primary key. I
> see that in the past, same issue was reported and possible solution
> was to run full vacuum or re-index. We tried both, but still able to
> enter duplicate records.
>
> This is blocker for us and would like to have solution for this as
> soon as possible.
You haven't provided information that'd allow us to debug the issue. At the very least we'd need table + index definition and the inserts that are allowed to happen despite being duplicates. Even better would be a way to reproduce the issue.
You said that you migrated from 13.7 to 14.7 recently - did you also change your OS version?
Greetings,
Andres Freund
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
@ 2023-06-28 20:59 ` Andres Freund <andres@anarazel.de>
2023-06-29 12:12 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andres Freund @ 2023-06-28 20:59 UTC (permalink / raw)
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi,
On 2023-06-28 08:38:03 +0000, Ajinkya Tankhiwale wrote:
> CREATE TABLE IF NOT EXISTS offer
> (
> action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
> action_type character varying COLLATE pg_catalog."default" NOT NULL,
> buy_sell_indicator character varying COLLATE pg_catalog."default" NOT NULL,
> creation_date_time timestamp without time zone NOT NULL,
> modification_date_time timestamp without time zone NOT NULL,
> CONSTRAINT pk_ado PRIMARY KEY (action_id)
> );
Just to check, the non-detected uniqueness violations are in this table?
How exactly are you inserting?
As the primary key is using a sequence, you shouldn't even get to the
situation that there could be a uniqueness violation.
> Interesting point here is, if I create new table then constraint violation is thrown, on old table I could enter duplicate records.
> Also, we migrated from el7 to el8.
If the relevant constraints were on some form of a string, I'd suspect that
you ran into a corrupted index due to collation changes between el7 and
el8. But the pkey of the table above is on a bigint...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-06-28 20:59 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
@ 2023-06-29 12:12 ` Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-07-02 21:53 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ajinkya Tankhiwale @ 2023-06-29 12:12 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello,
Please find below snippet from pgAdmin
[cid:image001.png@01D9AA93.93616B90]
I was able to enter duplicates from pgAdmin.
Regards,
Ajinkya
-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Wednesday, June 28, 2023 11:00 PM
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18002: Duplicate entries of row possible even after having primary key
"External email. Open with Caution"
Hi,
On 2023-06-28 08:38:03 +0000, Ajinkya Tankhiwale wrote:
> CREATE TABLE IF NOT EXISTS offer
> (
> action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
> action_type character varying COLLATE pg_catalog."default" NOT NULL,
> buy_sell_indicator character varying COLLATE pg_catalog."default" NOT NULL,
> creation_date_time timestamp without time zone NOT NULL,
> modification_date_time timestamp without time zone NOT NULL,
> CONSTRAINT pk_ado PRIMARY KEY (action_id) );
Just to check, the non-detected uniqueness violations are in this table?
How exactly are you inserting?
As the primary key is using a sequence, you shouldn't even get to the situation that there could be a uniqueness violation.
> Interesting point here is, if I create new table then constraint violation is thrown, on old table I could enter duplicate records.
> Also, we migrated from el7 to el8.
If the relevant constraints were on some form of a string, I'd suspect that you ran into a corrupted index due to collation changes between el7 and el8. But the pkey of the table above is on a bigint...
Greetings,
Andres Freund
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
Attachments:
image001.png (image/png, 35.3K) download
view image
* Re: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-06-28 20:59 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-29 12:12 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
@ 2023-07-02 21:53 ` David Rowley <dgrowleyml@gmail.com>
2023-07-03 01:06 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 10+ messages in thread
From: David Rowley @ 2023-07-02 21:53 UTC (permalink / raw)
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Fri, 30 Jun 2023 at 00:13, Ajinkya Tankhiwale
<ajinkya.tankhiwale@tcs.com> wrote:
> Please find below snippet from pgAdmin
>
> I was able to enter duplicates from pgAdmin.
It's very unclear what's going on here. In the screenshots you
included the problem table seems to be named trade.addressed_offer and
the primary key column seems to be pre_trade_action_id, yet a few
emails ago the problem table was "offer" with that primary key on the
"action_id" column.
It might be best if you start again and explain the problem and
include the table name of the table that is actually causing the
issue. Use psql instead of pgAdmin and show us the output of:
\d name_of_the_problem_table
then try executing the commands that you expect to fail but are not.
Include the output here. Include the same GROUP BY ... HAVING
COUNT(*) > 1 that shows the duplicates, then try performing a VACUUM
FULL. If you cannot afford the access exclusive lock, then at least a
CREATE UNIQUE INDEX CONCURRENTLY to show that it creates and includes
the duplicates.
As of now, this seems to be more likely due to operator error, so you
might need to start being a bit more concise to prove that's not the
case.
David
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-06-28 20:59 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-29 12:12 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-07-02 21:53 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key David Rowley <dgrowleyml@gmail.com>
@ 2023-07-03 01:06 ` Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Geoghegan @ 2023-07-03 01:06 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; Andres Freund <andres@anarazel.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sun, Jul 2, 2023 at 2:53 PM David Rowley <dgrowleyml@gmail.com> wrote:
> It might be best if you start again and explain the problem and
> include the table name of the table that is actually causing the
> issue. Use psql instead of pgAdmin and show us the output of:
>
> \d name_of_the_problem_table
They're on 14, so I suggest that pg_amcheck be used for this.
Ajinkya: Something like this ought to do it:
pg_amcheck --install-missing --heapallindexed -d postgres
What does that command output? Can you let us know?
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #18002: Duplicate entries of row possible even after having primary key
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
@ 2023-06-28 10:28 ` David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: David Rowley @ 2023-06-28 10:28 UTC (permalink / raw)
To: ajinkya.tankhiwale@tcs.com; pgsql-bugs@lists.postgresql.org
On Tue, 27 Jun 2023 at 23:24, PG Bug reporting form
<noreply@postgresql.org> wrote:
> We migrated from 13.7 to 14.7 recently, in current data setup, we say that
> duplicate records are present in table even after primary key. I see that in
> the past, same issue was reported and possible solution was to run full
> vacuum or re-index. We tried both, but still able to enter duplicate
> records.
This is quite strange. A VACUUM FULL will create a new heap and
indexes and copy over all tuples from the old heap. If there was some
index corruption, then this really should fix it.
Did you remove the duplicates before performing the VACUUM FULL?
Does the table still have duplicates now?
If so, it would be interesting to see the result of:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET LOCAL enable_seqscan=0;
EXPLAIN SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING
count(*)>1;
SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING count(*)>1;
RESET enable_seqscan;
SET LOCAL enable_indexscan=0;
SET LOCAL enable_indexonlyscan=0;
SET LOCAL enable_bitmapscan=0;
EXPLAIN SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING
count(*)>1;
SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING count(*)>1;
COMMIT;
David
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-07-03 01:06 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-14 02:01 BUG #5276: pg_ctl reads data directory on -D instead of postgresql.conf directoryh James Bellinger <jfb@zer7.com>
2010-01-14 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-27 11:03 BUG #18002: Duplicate entries of row possible even after having primary key PG Bug reporting form <noreply@postgresql.org>
2023-06-27 20:36 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-28 08:38 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-06-28 20:59 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Andres Freund <andres@anarazel.de>
2023-06-29 12:12 ` RE: BUG #18002: Duplicate entries of row possible even after having primary key Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>
2023-07-02 21:53 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key David Rowley <dgrowleyml@gmail.com>
2023-07-03 01:06 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key Peter Geoghegan <pg@bowt.ie>
2023-06-28 10:28 ` Re: BUG #18002: Duplicate entries of row possible even after having primary key David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox