Re: [HACKERS] Multiple psql -c / -f options
IMHO the current behavior is broken: decibel@decina:[17:46]~/pgsql/HEAD/i$bin/psql -c 'select 1' -c 'select 2' ?column? -- 2 (1 row) Another try with one -c but with similar results: sh psql -c SELECT 1; SELECT 'hello'; ?column? -- hello (1 row) sh psql -V psql (PostgreSQL) 9.3.1 -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
On 18/10/13 18:01, Amit Kapila wrote:
On Wed, Oct 16, 2013 at 5:55 PM, Stéphan BEUZE
stephan.be...@douane.finances.gouv.fr wrote:
The following query is performed concurrently by two threads logged in with
two different users:
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
FROM
pg_stat_activity
WHERE
usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
SELECT
nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
FROM (
SELECT
client_addr, pid, usename
FROM
raw_stat s
WHERE
NOT EXISTS (
SELECT
NULL
FROM
my_stat u
WHERE
current_date = u.creation
AND
s.pid = u.pid
AND
s.client_addr = u.client_addr
AND
s.usename = u.usename
)
) t;
From time to time, I get the following error: tuple concurrently updated
I can't figure out what throw this error and why this error is thrown. Can
you shed a light ?
I have tried by using this query in a loop of 5000 and run the loop
in 2 different connections with different users, but could not get the
error.
What I understood from sql statement is that it will insert new
rows when there are new/different connections, so simply running this
sql statement
from 2 connections might not insert any new rows.
a. Are there any new connections happening, how this table is
getting populated?
b. How did you concluded that above sql statement leads to error,
because this error doesn't seem to occur in path of above sql
statement.
c. Are there any other sql statements in connection where you see this
error?
Can you explain a bit more about your scenario, so that this error
can be reproduced easily.
---
Here is the sql definition of the table mystat.
**mystats.sql**
CREATE TABLE mystat
(
id bigint NOT NULL,
creation date NOT NULL DEFAULT current_date,
client_addr text NOT NULL,
pid integer NOT NULL,
usename name NOT NULL,
CONSTRAINT statistiques_connexions_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Some comments about SQL statements:
a. table name provided as part of schema (mystat) is different
from one used in sql statement(my_stat)
b. definition of sequence mystat_sequence is missing, although it
doesn't seem to be necessary, but if you can provide the definition
you are using
then it will be better.
Stephen - what framework or system are you using to run these two
threads? That sort of error looks very like the type of thing you would
get by sharing the connection object/pointer between two threads...
Cheers
Mark
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add min and max execute statement time in pg_stat_statement
I submit patch adding min and max execute statement time in pg_stat_statement in
next CF.
pg_stat_statement have execution time, but it is average execution time and does
not provide detail information very much. So I add min and max execute statement
time in pg_stat_statement columns. Usage is almost same as before. However, I
add
pg_stat_statements_reset_time() function to get min_time and max_time in the
specific period. This function resets or inits min and max execution time
before.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index e8aed61..5c63940 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,8 +4,10 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
- pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.2.sql \
+ pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--1.1--1.2.sql \
+ pg_stat_statements--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
index 5be281e..5662273 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
@@ -1,7 +1,7 @@
/* contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql */
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
-\echo Use ALTER EXTENSION pg_stat_statements UPDATE TO '1.1' to load this file. \quit
+\echo Use ALTER EXTENSION pg_stat_statements UPDATE to load this file. \quit
/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
deleted file mode 100644
index 42e4d68..000
--- a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
+++ /dev/null
@@ -1,43 +0,0 @@
-/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use CREATE EXTENSION pg_stat_statements to load this file. \quit
-
--- Register functions.
-CREATE FUNCTION pg_stat_statements_reset()
-RETURNS void
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION pg_stat_statements(
-OUT userid oid,
-OUT dbid oid,
-OUT query text,
-OUT calls int8,
-OUT total_time float8,
-OUT rows int8,
-OUT shared_blks_hit int8,
-OUT shared_blks_read int8,
-OUT shared_blks_dirtied int8,
-OUT shared_blks_written int8,
-OUT local_blks_hit int8,
-OUT local_blks_read int8,
-OUT local_blks_dirtied int8,
-OUT local_blks_written int8,
-OUT temp_blks_read int8,
-OUT temp_blks_written int8,
-OUT blk_read_time float8,
-OUT blk_write_time float8
-)
-RETURNS SETOF record
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
--- Register a view on the function for ease of use.
-CREATE VIEW pg_stat_statements AS
- SELECT * FROM pg_stat_statements();
-
-GRANT SELECT ON pg_stat_statements TO PUBLIC;
-
--- Don't want this to be available to non-superusers.
-REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql b/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
index e84a3cb..0addba0 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
@@ -4,5 +4,6 @@
\echo Use CREATE EXTENSION pg_stat_statements to load this file. \quit
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset();
+ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset_time();
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements();
ALTER EXTENSION pg_stat_statements ADD view pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ea930af..8f9b641 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -76,6 +76,7 @@ static const uint32 PGSS_FILE_HEADER = 0x20120328;
#define USAGE_DECREASE_FACTOR (0.99) /* decreased every entry_dealloc */
#define STICKY_DECREASE_FACTOR (0.50) /* factor for sticky entries */
#define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */
+#define EXEC_TIME_INIT (-1) /* initial execution time */
#define JUMBLE_SIZE1024 /* query serialization buffer size */
@@ -102,6 +103,8 @@ typedef struct Counters
{
int64 calls; /* # of times executed */
double total_time; /* total execution time, in msec */
+ double min_time;
[HACKERS] Improvement of pg_stat_statement usage about buffer hit ratio
Hi, I submit improvement of pg_stat_statement usage patch in CF3. In pg_stat_statement, I think buffer hit ratio is very important value. However, it is difficult to calculate it, and it need complicated SQL. This patch makes it more simple usage and documentation. -bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / - nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent +bench=# SELECT query, calls, total_time, rows, shared_blks_hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; It will be very simple:-) This patch conflicts pg_stat_statement_min_max_exectime patch which I submitted, and pg_stat_statement_min_max_exectime patch also adds new columns which are min_time and max_time. So I'd like to change it in this opportunity. Regards, -- Mitsumasa KONDO NTT Open Source Software Center diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index e8aed61..5c63940 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -4,8 +4,10 @@ MODULE_big = pg_stat_statements OBJS = pg_stat_statements.o EXTENSION = pg_stat_statements -DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \ - pg_stat_statements--unpackaged--1.0.sql +DATA = pg_stat_statements--1.2.sql \ + pg_stat_statements--1.0--1.1.sql \ + pg_stat_statements--1.1--1.2.sql \ + pg_stat_statements--unpackaged--1.0.sql ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql index 5be281e..5662273 100644 --- a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql +++ b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql @@ -1,7 +1,7 @@ /* contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql */ -- complain if script is sourced in psql, rather than via ALTER EXTENSION -\echo Use ALTER EXTENSION pg_stat_statements UPDATE TO '1.1' to load this file. \quit +\echo Use ALTER EXTENSION pg_stat_statements UPDATE to load this file. \quit /* First we have to remove them from the extension */ ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements; diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql new file mode 100644 index 000..f0a8e0f --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql @@ -0,0 +1,63 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use ALTER EXTENSION pg_stat_statements UPDATE to load this file. \quit + +/* First we have to remove them from the extension */ +ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements; +ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(); + +/* Then we can drop them */ +DROP VIEW pg_stat_statements; +DROP FUNCTION pg_stat_statements(); + +/* Now redefine */ +CREATE FUNCTION pg_stat_statements( +OUT userid oid, +OUT dbid oid, +OUT query text, +OUT calls int8, +OUT total_time float8, +OUT rows int8, +OUT shared_blks_hit int8, +OUT shared_blks_read int8, +OUT shared_blks_dirtied int8, +OUT shared_blks_written int8, +OUT local_blks_hit int8, +OUT local_blks_read int8, +OUT local_blks_dirtied int8, +OUT local_blks_written int8, +OUT temp_blks_read int8, +OUT temp_blks_written int8, +OUT blk_read_time float8, +OUT blk_write_time float8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME' +LANGUAGE C; + +CREATE VIEW pg_stat_statements AS + SELECT userid, + dbid, + query, + calls, + total_time, + rows, + CASE WHEN shared_blks_hit + shared_blks_read 0 + THEN 100.0 * (shared_blks_hit::float / (shared_blks_hit + shared_blks_read)) + ELSE 0 END AS shared_blks_hit_percent, + shared_blks_hit, + shared_blks_read, + shared_blks_dirtied, + shared_blks_written, + local_blks_hit, + local_blks_read, + local_blks_dirtied, + local_blks_written, + temp_blks_read, + temp_blks_written, + blk_read_time, + blk_write_time + FROM pg_stat_statements(); + +GRANT SELECT ON pg_stat_statements TO PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql deleted file mode 100644 index 42e4d68..000 --- a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql +++ /dev/null @@ -1,43 +0,0 @@ -/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use CREATE EXTENSION pg_stat_statements to load this file. \quit - --- Register functions. -CREATE FUNCTION pg_stat_statements_reset()
[HACKERS] Re: space reserved for WAL record does not match what was written: panic on windows
On Fri, Oct 18, 2013 at 1:39 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Oct 11, 2013 at 1:14 AM, Noah Misch n...@leadboat.com wrote: On Thu, Oct 10, 2013 at 03:23:30PM +0200, Andres Freund wrote: On 2013-10-10 08:59:47 -0400, Robert Haas wrote: On Tue, Oct 8, 2013 at 6:24 PM, Andres Freund and...@2ndquadrant.com wrote: Do you have a better alternative? Making the computation unconditionally 64bit will have a runtime overhead and adding a StaticAssert in the existing macro doesn't work because we use it in array sizes where gcc balks. We could try using inline functions, but that's not going to be pretty either. I don't really see that many further usecases that will align 64bit values on 32bit platforms, so I think we're ok for now. I'd be inclined to make the computation unconditionally 64-bit. I doubt the speed penalty is enough to worry about, and I think we're going to have more and more cases where optimizing for 32-bit platforms is just not the right decision. MAXALIGN is used in several of PG's hottest functions in many scenarios. att_align_nominal is used in slot_deform_tuple, heap_deform_tuple, nocachegetattr, etc. So I don't think that's viable yet. At least not with much more benefit than this... Agreed. Besides performance, aligning a wider-than-pointer value is an unusual need; authors should think thrice before doing that. I might have even defined the MAXALIGN64 macro in xlog.c rather than a core header. Incidentally, why does MAXALIGN64 use unsigned math while MAXALIGN uses signed math? Well, if this is the consensus, then I think the dynamic shared memory patch may need some revision. In that patch, I used uint64 to represent the size of the dynamic shared memory segment, sort of on the theory that we were going to use this to be allocating big chunks of dynamic shared memory for stuff like parallel sort. In follow-on patches I'm currently developing to actually do stuff with dynamic shared memory, this results in extensive use of MAXALIGN64, and it really kind of looks like it wants the whole set of alignment macros, not just that one. So option one is to leave the dsm code alone and add the rest of the macros. For me I don't really see why there's a need to use MAXALIGN64 for any memory addresses related to RAM. I only created MAXALIGN64 because I needed it to fix the WAL code which needed as 64bit type on all platforms, not just 64bit ones. For me it made perfect sense, so I'm a bit confused at most of this fuss. Though I do understand that it's a bit weird that both macros are almost the same on a 64 bit machine... As for signed vs unsigned, I've not looked at all of the places where MAXALIGN is used, but I just assumed it was for memory addresses, if this is the case then I'm confused why we'd ever want a negative valued memory address? This might be an obvious one, but can anyone tell me why the casts are in the macro at all? Can a compiler not decide for itself which type it should be using? Regards David Rowley But if we're bent on minimizing the use of 64-bit arithmetic on 32-bit systems, then presumably I should instead go back and retrofit that patch to use Size rather than uint64 to represent the size of a segment. But then I have two concerns: 1. Is there any guarantee that sizeof(intptr_t) = sizeof(size_t)? (Note that Size is just a typedef for size_t, in c.h) 2. If intptr_t is a signed type, as it appears to be, and size_t is an unsigned type, as I believe it to be, then is it safe to use the macros written for the signed type with a value of the unsigned type? Off-hand I can't see a problem there, but I'm not certain I'm not missing something. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] ERROR : 'tuple concurrently updated'
Here I provide more details about the environment where the error occurs:
* ENVIRONMENT
Client:
Java Web Application running on JBoss 5.0.0.GA - JDK 1.6.0_24 64bit
Server:
Postgresql 9.2.4, compiled by Visual C++ build 1600, 64bit
Client and Server run on the same platform:
Windows 7 Professional SP1 (2009)
* STRUCTURES
CREATE ROLE rec LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
CREATE ROLE rec_lct LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
CREATE SCHEMA rec AUTHORIZATION rec;
GRANT ALL ON SCHEMA rec TO rec;
GRANT USAGE ON SCHEMA rec TO rec_lct;
ALTER ROLE rec SET search_path = rec;
ALTER ROLE rec_lct SET search_path = rec;
SET SCHEMA 'rec'
CREATE SEQUENCE stats_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 120
CYCLE;
ALTER TABLE stats_sequence OWNER TO rec;
GRANT ALL ON TABLE stats_sequence TO rec;
GRANT UPDATE ON TABLE stats_sequence TO rec_lct;
CREATE TABLE my_stat
(
id bigint NOT NULL,
creation date NOT NULL DEFAULT current_date,
client_addr text NOT NULL,
pid integer NOT NULL,
usename name NOT NULL,
CONSTRAINT my_stat _pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE statistiques_connexions OWNER TO rec;
GRANT ALL ON TABLE statistiques_connexions TO rec;
GRANT SELECT, INSERT ON TABLE statistiques_connexions TO rec_lct;
CREATE INDEX statistiques_connexions_idx_creation
ON statistiques_connexions
USING btree
(creation);
CREATE INDEX statistiques_connexions_idx_ukey
ON statistiques_connexions
USING btree
(creation, pid, client_addr COLLATE pg_catalog.default, usename);
* CONTEXT
Two Java threads are created. One is connected with 'rec' user, while
the other one
is connected with 'rec_lct' user.
The threads don't create themselves their JDBC connections.
Instead, they each have their own pooled datasource preconfigured.
The pooled datasources are managed by the same connection pool
library: c3p0 0.9.1. The pooled datasources each open 3 connections
on startup. They can make this number of connections variate from 1 to 5
connections.
In our development context, this number of connections stay at 3.
The threads run the following query every 500 ms.
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
FROM
pg_stat_activity
WHERE
usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
SELECT
nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
FROM (
SELECT
client_addr, pid, usename
FROM
raw_stat s
WHERE
NOT EXISTS (
SELECT
NULL
FROM
my_stat u
WHERE
current_date = u.creation
AND
s.pid = u.pid
AND
s.client_addr = u.client_addr
AND
s.usename = u.usename
)
) t;
What can be observed first is that, at the beginning, everything run
smoothly.
Then unpredictably, the error 'tuple concurrently updated' appears...
Needless to say, that it disappears too... unpredictably.
Sometimes, it can shows up contisnously.
Tell me if you need some more detailed information.
Stephan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
You may find additional answers in my last email. However, I'll try to adress some of your questions. a. Are there any new connections happening, how this table is getting populated? Check my last email. b. How did you concluded that above sql statement leads to error, because this error doesn't seem to occur in path of above sql statement. The errors appear when I added the second threads. c. Are there any other sql statements in connection where you see this error? This is the only statement that generat this error. Can you explain a bit more about your scenario, so that this error can be reproduced easily. Please check my last full detailed email. Some comments about SQL statements: a. table name provided as part of schema (mystat) is different from one used in sql statement(my_stat) Sorry, for the typos b. definition of sequence mystat_sequence is missing, although it doesn't seem to be necessary, but if you can provide the definition you are using then it will be better. The definition of the sequence is provided in my detailed email among other things too. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On Fri, Oct 18, 2013 at 1:13 PM, Jaime Casanova ja...@2ndquadrant.com wrote:
= Code functionality =
+ {restore_command, PGC_POSTMASTER, WAL_ARCHIVE_RECOVERY,
+ {archive_cleanup_command, PGC_POSTMASTER, WAL_ARCHIVE_RECOVERY,
+ {recovery_end_command, PGC_POSTMASTER, WAL_ARCHIVE_RECOVERY,
+ {recovery_target_xid, PGC_POSTMASTER, WAL_RECOVERY_TARGET,
+ {recovery_target_name, PGC_POSTMASTER, WAL_RECOVERY_TARGET,
+ {recovery_target_time, PGC_POSTMASTER, WAL_RECOVERY_TARGET,
+ {trigger_file, PGC_POSTMASTER, REPLICATION_STANDBY,
Not sure about these ones
+ {recovery_target_timeline, PGC_POSTMASTER, WAL_RECOVERY_TARGET,
+ {primary_conninfo, PGC_POSTMASTER, REPLICATION_STANDBY,
It would be really nice to change these on the fly; it would help a lot
of issues with minor changes to replication config. I can understand,
though, that the replication code might not be prepared for that.
well, archive_command can be changed right now with a SIGHUP so at
least that one shouldn't change... and i don't think most of these are
too different. even if we are not sure we can do this now and change
them as SIGHUP later
Changing those parameters don't really matter as long as the node is
not performing a recovery IMO, but I'd rather see a careful approach
here and let all those parameters as PGC_POSTMASTER for now to avoid
any surprises. Perhaps a second patch on top of this one could be the
addition of context name like SIGHUP_RECOVERY, aka just allow those
parameters to be updated with SIGHUP as long as the node is not in
recovery.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
What PostgreSQL version is this? I'm using Postgresql 9.2.4, compiled by Visual C++ build 1600, 64-bit Are there any triggers on any of these tables? There are no triggers. Any noteworthy extensions installed? Here is the results returned by select * from pg_available_extensions Those extensions are installed in the system, so you can install them in PostgreSQL. You may also have contrib run by servers without being pure extension. So the question is about used extensions or contrib. (it can be loaded by server, or in a session with LOAD, it can be auto-explain, pg_stat_statement, ). There are actually two places where that error can happen: simple_heap_update and simple_heap_delete. If you set the error verbosity to verbose, you should be able to see which function is at fault. The thing is, I don't see anything in that query which would update or delete any tuples, so there must be more to the story. If you have the ability to build from source, you could try setting a long sleep just before that error is thrown. Then run your test case until it hangs at that spot and get a stack backtrace. But that may be more troubleshooting than you want to get into. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] logical changeset generation v6.4
On Mon, Oct 14, 2013 at 9:12 AM, Andres Freund and...@2ndquadrant.com wrote: Attached you can find version 6.4 of the patchset: So I'm still unhappy with the arbitrary logic in what's now patch 1 for choosing the candidate key. On another thread, someone mentioned that they might want the entire old tuple, and that got me thinking: there's no particular reason why the user has to want exactly the columns that exist in some unique, immediate, non-partial index (what a name). So I have two proposals: 1. Instead of allowing the user to choose the index to be used, or picking it for them, how about if we let them choose the old-tuple columns they want logged? This could be a per-column option. If the primary key can be assumed known and unchanging, then the answer might be that the user wants *no* old-tuple columns logged. Contrariwise someone might want everything logged, or anything in the middle. 2. If that seems too complicated, how about just logging the whole old tuple for version 1? I'm basically fine with the rest of what's in the first two patches, but we need to sort out some kind of consensus on this issue. Thanks, -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding new syntax in postgre sql
On 10/16/2013 01:17 PM, ankit bhardwaj wrote: I am new to postgre sql .And i want to add some new feature to postgresql As a startup i have taken the project to add syntax for table partitioning Welcome to the list! There has been some previous work done on adding this syntax, but I have not followed it so I do not know the details or if anyone is currently working on it (have not seen any activity about it recently on the list though). There is a patch linked from the wiki page about adding a partitioning syntax which is probably worth looking at. https://wiki.postgresql.org/wiki/Table_partitioning#Active_Work_In_Progress -- Andreas Karlsson -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On Thu, Oct 17, 2013 at 5:41 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/17/13 12:45 PM, Robert Haas wrote: The attached patch, which I propose to apply relatively soon if nobody objects, removes the IRIX port. +1 Done. And here's a patch for removing the alpha architecture and Tru64 UNIX (aka OSF/1) which runs on that architecture, per discussion upthread. Barring objections, I'll apply this next week. On a related note, I think we should update the paragaraph in installation.sgml that begins In general, PostgreSQL can be expected to work on these CPU architectures. Any architecture that doesn't have a buildfarm animal should be relegated to the second sentence, which reads Code support exists for ... but these architectures are not known to have been tested recently. Similarly, I think the following paragraph should be revised so that only operating systems for which we have current buildfarm support are considered fully supported. Others should be relegated to a sentence later in the paragraph that says something like code support exists but not tested recently or expected to work but not tested regularly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company remove-alpha-arch.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add min and max execute statement time in pg_stat_statement
On 10/18/2013 04:02 AM, KONDO Mitsumasa wrote: I submit patch adding min and max execute statement time in pg_stat_statement in next CF. pg_stat_statement have execution time, but it is average execution time and does not provide detail information very much. So I add min and max execute statement time in pg_stat_statement columns. Usage is almost same as before. However, I add pg_stat_statements_reset_time() function to get min_time and max_time in the specific period. This function resets or inits min and max execution time before. If we're going to extend pg_stat_statements, even more than min and max I'd like to see the standard deviation in execution time. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
Those extensions are installed in the system, so you can install them in PostgreSQL. You may also have contrib run by servers without being pure extension. So the question is about used extensions or contrib. (it can be loaded by server, or in a session with LOAD, it can be auto-explain, pg_stat_statement, ). I don't use any used extensions or contrib. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] COPY table FROM STDIN doesn't show count tag
From the following mail, copy behaviour between stdin and normal file having some inconsistency. http://www.postgresql.org/message-id/ce85a517.4878e%tim.k...@gmail.com The issue was that if copy execute from stdin, then it goes to the server to execute the command and then server request for the input, it sends back the control to client to enter the data. So once client sends the input to server, server execute the copy command and sends back the result to client but client does not print the result instead it just clear it out. Changes are made to ensure the final result from server get printed before clearing the result. Please find the patch for the same and let me know your suggestions. Thanks and Regards, Kumar Rajeev Rastogi copydefect.patch Description: copydefect.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multiple psql -c / -f options
On 10/18/2013 02:19 AM, Fabien COELHO wrote: IMHO the current behavior is broken: decibel@decina:[17:46]~/pgsql/HEAD/i$bin/psql -c 'select 1' -c 'select 2' ?column? -- 2 (1 row) Another try with one -c but with similar results: sh psql -c SELECT 1; SELECT 'hello'; ?column? -- hello (1 row) sh psql -V psql (PostgreSQL) 9.3.1 It's not broken. All this behaviour is documented fairly explicitly. See http://www.postgresql.org/docs/current/static/app-psql.html For example, regarding Fabio's example, which is actually very different from Jim's, the docs say: only the result of the last SQL command is returned. If you want to argue that it should be enhanced, then do. But it's acting as designed and as documented. I suspect changing this might actually have more wrinkles that you imagine, but I could be wrong. Incidentally, both of you could probably achieve what you apparently want with: echo 'some sql here' | psql cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
Just to be pedantic, commit message shows support for Tru64 ended in 201. I think you mean 2012. On 18/10/2013 13:41, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 17, 2013 at 5:41 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/17/13 12:45 PM, Robert Haas wrote: The attached patch, which I propose to apply relatively soon if nobody objects, removes the IRIX port. +1 Done. And here's a patch for removing the alpha architecture and Tru64 UNIX (aka OSF/1) which runs on that architecture, per discussion upthread. Barring objections, I'll apply this next week. On a related note, I think we should update the paragaraph in installation.sgml that begins In general, PostgreSQL can be expected to work on these CPU architectures. Any architecture that doesn't have a buildfarm animal should be relegated to the second sentence, which reads Code support exists for ... but these architectures are not known to have been tested recently. Similarly, I think the following paragraph should be revised so that only operating systems for which we have current buildfarm support are considered fully supported. Others should be relegated to a sentence later in the paragraph that says something like code support exists but not tested recently or expected to work but not tested regularly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical changeset generation v6.4
On Fri, Oct 18, 2013 at 7:11 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 14, 2013 at 9:12 AM, Andres Freund and...@2ndquadrant.com wrote: Attached you can find version 6.4 of the patchset: So I'm still unhappy with the arbitrary logic in what's now patch 1 for choosing the candidate key. On another thread, someone mentioned that they might want the entire old tuple, and that got me thinking: there's no particular reason why the user has to want exactly the columns that exist in some unique, immediate, non-partial index (what a name). So I have two proposals: Aside: what's an immediate index? Is this speaking to the constraint? (immediate vs deferred?) merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On Fri, Oct 18, 2013 at 9:39 AM, Tim Kane tim.k...@gmail.com wrote: Just to be pedantic, commit message shows support for Tru64 ended in 201. I think you mean 2012. Duh, I'm a dork. Thanks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] fdw_private and (List*) handling in FDW API
Hi,
I've been exploring the new FDW API in the past few days, and I'm slightly
confused by the fdw_private fields. A few comments:
1) Generally all the API functions pass data using fields in the nodes
(e.g. GetForeignRelSize uses baserel-fdw_private etc.), but
PlanForeignModify simply returns the data, and BeginForeignModify accepts
that as a regular parameter. Is there any particular reason not to adapt
the same approach in all cases, i.e. either return the private data in all
cases (and pass as parameters), or passing them inside node/plan/...?
2) Is there any particular reason why PlanForeignModify/BeginForeignModify
require the fdw_private to be a List*, and not a generic pointer? I mean,
RelOptInfo declares fdw_private as a (void*) but the other structures
(e.g. ForeignScan) switches to (List*) for some reason. But all the
optimizer does with this data is this in createplan.c
fdw_private_list = NIL;
i = 0;
foreach(lc, resultRelations)
{
...
fdw_private = fdwroutine-PlanForeignModify(root, node, rti, i);
fdw_private_list = lappend(fdw_private_list, fdw_private);
i++;
}
node-fdwPrivLists = fdw_private_list;
return node;
If I read that correctly, it just accumulates all the lists into a single
list (and then unpacks that into individual lists in nodeModifyTable.c).
What is the reason for using (List*) here? I'd rather use a structure
here, not generic lists, YMMV. Or is there something I missed (e.g. future
plans)?
regards
Tomas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updatable view columns
On Tue, Sep 17, 2013 at 6:16 PM, Marko Tiikkaja ma...@joh.to wrote: On 2013-09-17 12:53, Dean Rasheed wrote: Thanks for the review. Those changes all look sensible to me. Here's an updated patch incorporating all your fixes, and rebased to apply without offsets. Looks good to me. Marking this one ready for committer. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
=?ISO-8859-1?Q?St=E9phan_BEUZE?= stephan.be...@douane.finances.gouv.fr writes: So the question is about used extensions or contrib. (it can be loaded by server, or in a session with LOAD, it can be auto-explain, pg_stat_statement, ). I don't use any used extensions or contrib. Well, you're doing *something* that you have not told us about. As Robert said, the only places where that error can be thrown are simple_heap_update and simple_heap_delete, and neither of those are reachable from an INSERT command unless something is happening behind the scenes. Maybe you have an ON INSERT trigger on that table? Another point here is that the NOT EXISTS coding seems to be trying to prevent insertion of any duplicate rows into my_stat, but it will fail miserably as soon as there are multiple processes doing that command concurrently, since the NOT EXISTS check will only examine rows that were committed before the command starts, not any that get committed while it runs. I wonder whether you have code you've not shown us that depends on the assumption of no duplicates in my_stat, and will lead to multiple-update attempts somewhere else as soon as such duplicates appear. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch to compute Max LSN of Data Pages
On Wed, Oct 9, 2013 at 2:28 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Sep 14, 2013 at 3:03 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Monday, July 08, 2013 5:16 PM Andres Freund wrote: On 2013-07-08 17:10:43 +0530, Amit Kapila wrote: On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? No, you are not missing anything. It can be only used to find max LSN in database which can avoid further corruption Why is the patch submitted documenting it as a use-case then? I find it rather scary if the *patch authors* document a known unsafe use case as one of the known use-cases. I got the problem which can occur with the specified use case. Removed the wrong use case specified above. Thanks for the review, please find the updated patch attached in the mail. Patch is not getting compiled on Windows, I had made following changes: a. updated the patch for resolving Windows build b. few documentation changes in (pg_resetxlog.sgml) for spelling mistake and minor line change c. corrected year for Copyright in file pg_computemaxlsn.c I am OK with this patch in its current form, modulo some grammar issues in the documentation which I can fix before committing. However, I'm unclear whether there was sufficient consensus to proceed with this. Can others weigh in? If there is too much residual unhappiness with this, then we should just mark this as Rejected and stop wasting time on it; it can be pushed to PGXN or similar even if we don't put it in core. I didn't hear any other votes. Anyone else have an opinion about this? If I can't get a +1 from anyone who wasn't involved in writing the patch, I'm inclined to think we don't have sufficient consensus to commit this. On further review of the patch, I also found a number of other issues that I think need to be fixed before we could consider committing it: - Per a previous request of mine, the patch has three different modes: it can be run on an individual file, on a directory potentially containing multiple relation files, or on an entire data directory. This is not explained in the documentation. - The patch skips printing an error if attempting to open a file returns ENOENT. I don't see why that case shouldn't print an error. Yeah, it could be legit if you're executing this against a running server, but why are you doing that? And even if you are (e.g. for corruption detection), printing a error message and proceeding makes more sense than proceeding without printing anything, at least IMHO. - Some of the static functions in this file preceed main and others follow it. And they use different naming conventions. I suggest putting all of them after main() and using names like check_data_dir, check_data_file_name (instead of validateRelfilenodename; note that the comment for that function is also incorrect), find_max_lsn_in_file, find_max_lsn_in_directory, find_max_lsn_in_pgdata. - Since the patch goes to some pains to exit with 0 if no errors were encountered and 1 if any were, that probably ought to be documented. But I think instead of passing a result argument back up the call stack it would be better to just keep a global variable called errors_encountered. When we encounter an error, bump that value. Then you can just do exit(errors_encountered 0 ? 1 : 0) and not bother passing all of this stuff around via the return value. The current behavior is inconsistent in another way, too: if you encounter an error while scanning through a particular directory, you finish the whole directory. But if multiple command-line arguments were passed, you don't proceed to any subsequent command-line arguments. I think you should continue always, which the above-mentioned change will take care of basically for free. - The description of the -q option is not very clear. A reader could be forgiven for thinking that the option suppresses all output, which would be quite useless, or at least left in doubt about what output will still be provided. A broader complaint I have with this patch is that it almost but not-quite solves a problem I've had a few times in the past: namely, searching through the data directory for data blocks which have LSNs in the future. This has come up a few times for me, and this tool would make it easier, because I'd be able to run it and look through the output to see which relations have high max-LSN values. However, it wouldn't be quite enough, because it'd only tell me about the block with the highest LSN in each file, whereas what I'd really want to find is every block with an LSN greater than some
Re: [HACKERS] psql tab completion for updatable foreign tables
On Fri, Oct 18, 2013 at 1:34 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: Personally, I think this is too fancy anyway. I'd just complete all views and foreign tables and be done with it. We don't inspect permissions either, for example. This might be too confusing for users. Yeah, I think you're probably right. I tend to agree. When the rules were simple (i.e. pretty much nothing was updateable) it might have made sense to make tab completion hew to them, but they're complex enough now that I think it no longer does. There are now three different ways that a view can be updateable (auto, trigger, rule) and the rules are complex. Based on that it sounds like we need a new version of this patch. If that's not going to happen RSN, we should mark this returned with feedback and it can be resubmitted if and when someone finds the time to update it. Thanks, -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fdw_private and (List*) handling in FDW API
Tomas Vondra t...@fuzzy.cz writes: 2) Is there any particular reason why PlanForeignModify/BeginForeignModify require the fdw_private to be a List*, and not a generic pointer? That data has to be copiable by copyObject(), which a generic void* is not. We could perhaps have made it Node* instead, but that would only work conveniently if there were infrastructure for plugins to create new first-class Node types; which there isn't. A List is often the easiest way to transport a few random values from plan time to execution time, so it seemed best to declare fdw_private that way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch to compute Max LSN of Data Pages
Robert Haas escribió: A broader complaint I have with this patch is that it almost but not-quite solves a problem I've had a few times in the past: namely, searching through the data directory for data blocks which have LSNs in the future. This has come up a few times for me, and this tool would make it easier, because I'd be able to run it and look through the output to see which relations have high max-LSN values. However, it wouldn't be quite enough, because it'd only tell me about the block with the highest LSN in each file, whereas what I'd really want to find is every block with an LSN greater than some threshold value. Maybe I'm pushing the envelope too much by trying to fit that into the framework of this patch, but I can't help thinking we're not going to want both pg_computemaxlsn and pg_findlsnsaftersomethreshold that are 95% the same code, so maybe we ought to rename the utility to something slightly more generic than pg_computemaxlsn. Perhaps not coincidentally, I had a need to do this recently. Perhaps we should turn the utility into a generic tool to report existing LSNs, with options to 1) report only the highest one in a given file, 2) report only those that exceed some threshold. So maybe pg_reportlsn or pg_extractlsn. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fdw_private and (List*) handling in FDW API
On 18 Říjen 2013, 17:52, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: 2) Is there any particular reason why PlanForeignModify/BeginForeignModify require the fdw_private to be a List*, and not a generic pointer? That data has to be copiable by copyObject(), which a generic void* is not. We could perhaps have made it Node* instead, but that would only work conveniently if there were infrastructure for plugins to create new first-class Node types; which there isn't. A List is often the easiest way to transport a few random values from plan time to execution time, so it seemed best to declare fdw_private that way. Oh, I see. Thanks for explanation. Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem
All, So, I did an informal survey last night a SFPUG, among about 30 PostgreSQL DBAs and developers. While hardly a scientific sample, it's a data point on what we're looking at for servers. Out of the 30, 6 had one or more production instances of PostgreSQL running on machines or VMs with less than 1GB of RAM. Out of those 5 had already edited their PostgreSQL.conf extensively. Perhaps more importantly, for four out of the 6, the low-memory Postgres instance(s) was an older version (8.2 to 9.0) which they did not expect to upgrade. Also, note that a couple of the 6 were consultants, so they were speaking for dozens of customer servers. As a second data point, Christophe and I did a quick survey of the database of server information on our clients, which include a bunch of cloud-hosted web companies. We found two PostgreSQL VMs which did not have 1GB or more RAM, out of a few hundred. Now, obviously, there's some significant sample bias in the above, but I think it gives support to the assertion that we shouldn't really be worrying about PostgresQL running well out-of-the-box on machines with 1GB of RAM. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] pg_sleep(interval)
On 10/17/2013 01:41 PM, Vik Fearing wrote: Perhaps; but it has also been an example of the benefits of having tight review. FWIW, I agree. I have been impressed by the rigorous review process of this project ever since I started following it. OK, good! That makes me feel better. So, I surveyed 30 members of the San Francisco PostgreSQL User Group last night. Out of the 30: 4 had ever used pg_sleep(), and those four included Jeff Davis and Peter G. I asked the remaining two about the new versions of pg_sleep, and they were more interested in pg_sleep_until(), and not particularly interested in pg_sleep(interval). So, to my mind backwards compatibility (the ambiguity issue) is insignificant because there are so few users of pg_sleep(), but there are serious questions about the demand for improvements on pg_sleep for that reason. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On 10/18/2013 02:41 PM, Robert Haas wrote: On Thu, Oct 17, 2013 at 5:41 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/17/13 12:45 PM, Robert Haas wrote: The attached patch, which I propose to apply relatively soon if nobody objects, removes the IRIX port. +1 Done. And here's a patch for removing the alpha architecture and Tru64 UNIX (aka OSF/1) which runs on that architecture, per discussion upthread. Barring objections, I'll apply this next week. hmm there are still some operating systems that officially support the alpha architecture which will likely result in problems for their ports. One example is OpenBSD both the current version (5.3) as well as the upcoming release do fully support alpha and have binary packages and source ports for postgresql and afaik they have no intention to stop supporting that plattform. On a related note, I think we should update the paragaraph in installation.sgml that begins In general, PostgreSQL can be expected to work on these CPU architectures. Any architecture that doesn't have a buildfarm animal should be relegated to the second sentence, which reads Code support exists for ... but these architectures are not known to have been tested recently. Similarly, I think the following paragraph should be revised so that only operating systems for which we have current buildfarm support are considered fully supported. Others should be relegated to a sentence later in the paragraph that says something like code support exists but not tested recently or expected to work but not tested regularly. seems like an improvement to me. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On 2013-10-18 18:24:58 +0200, Stefan Kaltenbrunner wrote: On 10/18/2013 02:41 PM, Robert Haas wrote: On Thu, Oct 17, 2013 at 5:41 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/17/13 12:45 PM, Robert Haas wrote: The attached patch, which I propose to apply relatively soon if nobody objects, removes the IRIX port. +1 Done. And here's a patch for removing the alpha architecture and Tru64 UNIX (aka OSF/1) which runs on that architecture, per discussion upthread. Barring objections, I'll apply this next week. hmm there are still some operating systems that officially support the alpha architecture which will likely result in problems for their ports. One example is OpenBSD both the current version (5.3) as well as the upcoming release do fully support alpha and have binary packages and source ports for postgresql and afaik they have no intention to stop supporting that plattform. Hm. If you read their status page (which I think you linked to before): http://openbsd.org/alpha.html you can find stuff like X11 not working. So I don't see that forcing us to much. Note also that we already don't support all openbsd platforms. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
Jaime, Except that we'll want 9.4's -R to do something, probably create a file called conf.d/replication.conf. Mind you, it won't need the same wonky quoting stuff. Currently the patch uses -R to create the recovery trigger file Right, I'm saying that we'll want to do better than that for release, but that's dependant on committing the conf directory patch. Note that this change makes committing the conf.d patch extra-important; it's going to be a LOT easier to upgrade tools for 9.4 if we have that. well, after upgrade you should do checks. and even if it happens, after it happens once people will be aware of the change. now, some suggestions were made to avoid the problem. 1) read the file if exists last in the process of postgresql.conf, 2) add a GUC indicating if it should read it and include it (not using it as a trigger file). another one, 3) include in this release an include_if_exists directive and give a warning if it sees the file then include it, on next release remove the include_if_exists (at least that way people will be warned before breaking compatibility) I think all of these suggestions just make our code more complicated without improving the upgrade situation. The reason given (and I think it's pretty good) for erroring on recovery.conf is that we don't want people to accidentally take a server out of replication because they didn't check which version of PostgreSQL they are on. *on the other hand*, if we prevent creation of a configuration file named recovery.conf, then we block efforts to write backwards-compatible management utilities. and all tools and procedures that currently exists. Right. However, exploring your suggestions above, none of those workarounds prevent breakage. And in some cases, they make the breakage less obvious than the current patch does. If repmgr 1.2 / OmniPITR 1.2 won't work correctly with 9.4, then we want those tools to break at upgrade time, not later when the user is trying to fail over. For that matter, 9.4 is a very good time (relatively speaking) to break replication tools because the new logical replication is going to cause everyone to rev their tools anyway. This kind of breakage alone might end up being a good reason to call the next version 10.0. well, there should be good solutions... maybe we haven't thought them yet. anyway, we can't postpone the decision forever. we need to make a decision and stick with it otherwise this patch will be stalled N releases for no good reason I think if there were a good solution, sometime in the last 1.5 years someone would have suggested it. Gods know Simon has tried. exactly as it is now, if it sees the recovery trigger file, then it starts ArchiveRecovery and after it finish delete the file (the only difference) and increment the timeline OK, so if I'm doing a PITR recovery, I just put the recovery variables into postgresql.conf, then? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On 10/18/2013 06:29 PM, Andres Freund wrote: On 2013-10-18 18:24:58 +0200, Stefan Kaltenbrunner wrote: On 10/18/2013 02:41 PM, Robert Haas wrote: On Thu, Oct 17, 2013 at 5:41 PM, Peter Eisentraut pete...@gmx.net wrote: On 10/17/13 12:45 PM, Robert Haas wrote: The attached patch, which I propose to apply relatively soon if nobody objects, removes the IRIX port. +1 Done. And here's a patch for removing the alpha architecture and Tru64 UNIX (aka OSF/1) which runs on that architecture, per discussion upthread. Barring objections, I'll apply this next week. hmm there are still some operating systems that officially support the alpha architecture which will likely result in problems for their ports. One example is OpenBSD both the current version (5.3) as well as the upcoming release do fully support alpha and have binary packages and source ports for postgresql and afaik they have no intention to stop supporting that plattform. Hm. If you read their status page (which I think you linked to before): http://openbsd.org/alpha.html you can find stuff like X11 not working. So I don't see that forcing us to much. not sure that page is acurate and not sure how relevant X11 support is for postgresql :) Anyway they do currently have packages (9.2 in -stable and 9.3 in -current) available and I think we should consider packagers here as well - I personally don't have any particular need for alpha but it is clearly not as dead as some of the others we are discussing. Note also that we already don't support all openbsd platforms. sure - but does that also mean we should desupport without at least considering it? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On Fri, Oct 18, 2013 at 11:32 AM, Josh Berkus j...@agliodbs.com wrote: exactly as it is now, if it sees the recovery trigger file, then it starts ArchiveRecovery and after it finish delete the file (the only difference) and increment the timeline OK, so if I'm doing a PITR recovery, I just put the recovery variables into postgresql.conf, then? create a recovery trigger file (called standby.enabled in current patch) in $PGDATA and start the server -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On Thu, Oct 17, 2013 at 3:10 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 17, 2013 at 12:22 AM, Noah Misch n...@leadboat.com wrote: Removing support for alpha is a different animal compared to removing support for non-gcc MIPS and most of the others in your list. A hacker wishing to restore support for another MIPS compiler would fill in the assembly code blanks, probably using code right out of an architecture manual. A hacker wishing to restore support for alpha would find himself auditing every lock-impoverished algorithm in the backend. I had much the same thought last night. So I reverse my vote on Alpha: let's drop it. I had thought that perhaps there'd be some value in keeping it to force ourselves to consider what will happen under the weakest generally-understood memory model, but in fact that's probably a doomed effort without having the hardware available to test the code. As you say, any future atomics support for such a platform will be a major undertaking. FWIW, I think that if we approach coding lock free algorithms correctly - i.e. which memory barriers can we avoid while being safe, instead of which memory barriers we need to add to become safe - then supporting Alpha isn't a huge amount of extra work. We only need a couple of extra barriers after atomic reads where I think we should have a comment anyway explaining that we don't need a read barrier because a data dependency provides ordering. In general I agree that we are unlikely to provide a bug free result without a build farm animal, so I'm ±0 on removing support. We can try to support, but we are unlikely to succeed. I also find it unlikely that anyone will create a new architecture with a similarly loose memory model. The experience with Alpha and other microprocessors shows that the extra hardware needed for fast and strong memory ordering guarantees more than pays for itself in performance. Regards, Ants Aasma -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On Fri, Oct 18, 2013 at 9:55 AM, Ants Aasma a...@cybertec.at wrote: FWIW, I think that if we approach coding lock free algorithms correctly - i.e. which memory barriers can we avoid while being safe, instead of which memory barriers we need to add to become safe - then supporting Alpha isn't a huge amount of extra work. Alpha is completely irrelevant, so I would not like to expend the tiniest effort on supporting it. If there is someone using a very much legacy architecture like this, I doubt that even they will appreciate the ability to upgrade to the latest major version. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On 2013-10-18 18:36:03 +0200, Stefan Kaltenbrunner wrote: On 10/18/2013 06:29 PM, Andres Freund wrote: On 2013-10-18 18:24:58 +0200, Stefan Kaltenbrunner wrote: hmm there are still some operating systems that officially support the alpha architecture which will likely result in problems for their ports. One example is OpenBSD both the current version (5.3) as well as the upcoming release do fully support alpha and have binary packages and source ports for postgresql and afaik they have no intention to stop supporting that plattform. Hm. If you read their status page (which I think you linked to before): http://openbsd.org/alpha.html you can find stuff like X11 not working. So I don't see that forcing us to much. not sure that page is acurate and not sure how relevant X11 support is for postgresql :) Only in as much as it's a major piece of software missing. So removing pg isn't exactly a shocking thing. Anyway they do currently have packages (9.2 in -stable and 9.3 in -current) available and I think we should consider packagers here as well - I personally don't have any particular need for alpha but it is clearly not as dead as some of the others we are discussing. Note also that we already don't support all openbsd platforms. sure - but does that also mean we should desupport without at least considering it? We should consider it, yes. But I don't see it counting much in this case. Multiplatform OSs like (free|open|net)bsd and linux will mostly be the last to drop support for a platform. It will very rarely be the applications first. And openbsd still supporting it doesn't change the fact that there hasn't been new hardware for 10 years by the time 9.4 is going to be released. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On Fri, Oct 18, 2013 at 8:04 PM, Peter Geoghegan p...@heroku.com wrote: On Fri, Oct 18, 2013 at 9:55 AM, Ants Aasma a...@cybertec.at wrote: FWIW, I think that if we approach coding lock free algorithms correctly - i.e. which memory barriers can we avoid while being safe, instead of which memory barriers we need to add to become safe - then supporting Alpha isn't a huge amount of extra work. Alpha is completely irrelevant, so I would not like to expend the tiniest effort on supporting it. If there is someone using a very much legacy architecture like this, I doubt that even they will appreciate the ability to upgrade to the latest major version. It's mostly irrelevant and I wouldn't shed a tear for Alpha support, but I'd like to point out that it's a whole lot less irrelevant than some of the architectures being discussed here. The latest Alpha machines were sold only 6 years ago and supported up to 512GB of memory with 64 1.3 GHz cores, something that can run a very reasonable database load even today. Regards, Ants Aasma -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] removing old ports and architectures
On 10/17/2013 09:45 AM, Robert Haas wrote: According to http://en.wikipedia.org/wiki/UnixWare, UnixWare is not dead, although there have been no new releases in 5 years. Gee, I wonder why? I'll point out that SCO laid off all of its packagers three or four years ago. So nobody is packaging PostgreSQL for Unixware anymore. In general, I think we can reasonably expect that anyone still using Unixware isn't upgrading PostgreSQL, so if there's any effort at all in maintaining the port, we should dump it. The only reason we *have* the port in the first place is that SCO created it and used to maintain it. Oh, and +1 to dumping all of those other ports (IRIX, Tru64, Alpha), especially Alpha which I've been told is a maintenance issue. If we're concerned that there are users out there, the answer is to do some blogging and see if anyone speaks up. I'll post something. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time-Delayed Standbys
Hi all, The attached patch is a continuation of Robert's work [1]. I made some changes: - use of Latches instead of pg_usleep, so we don't have to wakeup regularly. - call HandleStartupProcInterrupts() before CheckForStandbyTrigger() because might change the trigger file's location - compute recoveryUntilDelayTime in XLOG_XACT_COMMIT and XLOG_XACT_COMMIT_COMPACT checks - don't care about clockdrift because it's an admin problem. Regards, [1] http://www.postgresql.org/message-id/BANLkTi==ttzhdqwzwjdjmof__8yua7l...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello time-delayed-standby-v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpgport vs libpgcommon
On 10/16/13 10:10 PM, Noah Misch wrote: dirmod.c perhaps deserves a split into libpgcommon parts (e.g. pgfnames()) and libpgport parts (e.g. pgrename()). I have also come to this realization. I propose to move pgfnames to src/common/pgfnames.c. Hopefully there's not much more. I have also come to this realization. ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical changeset generation v6.2
On 2013-10-14 09:36:03 -0400, Robert Haas wrote:
I thought and implemented that in the beginning. Unfortunately it's not
enough :(. That's probably the issue that took me longest to understand
in this patchseries...
Combocids can only fix the case where a transaction actually has create
a combocid:
1) TX1: INSERT id = 1 at 0/1: (xmin = 1, xmax=Invalid, cmin = 55, cmax =
Invalid)
2) TX2: DELETE id = 1 at 0/1: (xmin = 1, xmax=2, cmin = Invalid, cmax = 1)
So, if we're decoding data that needs to lookup those rows in TX1 or TX2
we both times need access to cmin and cmax, but neither transaction will
have created a multixact. That can only be an issue in transaction with
catalog modifications.
Oh, yuck. So that means you have to write an extra WAL record for
EVERY heap insert, update, or delete to a catalog table? OUCH.
So. As it turns out that solution isn't sufficient in the face of VACUUM
FULL and mixed DML/DDL transaction that have not yet been decoded.
To reiterate, as published it works like:
For every modification of catalog tuple (insert, multi_insert, update,
delete) that has influence over visibility issue a record that contains:
* filenode
* ctid
* (cmin, cmax)
When doing a visibility check on a catalog row during decoding of mixed
DML/DDL transaction lookup (cmin, cmax) for that row since we don't
store both for the tuple.
That mostly works great.
The problematic scenario is decoding a transaction that has done mixed
DML/DDL *after* a VACUUM FULL/CLUSTER has been performed. The VACUUM
FULL obviously changes the filenode and the ctid of a tuple, so we
cannot successfully do a lookup based on what we logged before.
I know of the following solutions:
1) Don't allow VACUUM FULL on catalog tables if wal_level = logical.
2) Make VACUUM FULL prevent DDL and then wait till all changestreams
have decoded up to the current point.
3) don't delete the old relfilenode for VACUUM/CLUSTERs of system tables
if there are life decoding slots around, instead delegate that
responsibility to the slot management.
4) Store both (cmin, cmax) for catalog tuples.
I bascially think only 1) and 4) are realistic. And 1) sucks.
I've developed a prototype for 4) and except currently being incredibly
ugly, it seems to be the most promising approach by far. My trick to
store both cmin and cmax is to store cmax in t_hoff managed space when
wal_level = logical.
That even works when changing wal_level from logical to logical
because only ever need to store both cmin and cmax for transactions that
have decodeable content - which they cannot yet have before wal_level =
logical.
This requires some not so nice things:
* A way to declare we're storing both. I've currently chosen
HEAP_MOVED_OFF | HEAP_MOVED_IN. That sucks.
* A way for heap_form_tuple to know it should add the necessary space to
t_hoff. I've added TupleDesc-tdhaswidecid for it.
* Fiddling with existing checks for HEAP_MOVED{,OFF,IN} to check for
both set at the same time.
* Changing the WAL logging to (optionally?) transport the current
CommandId instead of always resetting it InvalidCommandId.
The benefits are:
* Working VACUUM FULL
* Much simpler tqual.c logic, everything is stored in the row itself. No
hash or something like that built.
* No more need to log (relfilenode, cmin, cmax) separately from heap
changes itself anymore.
In the end, the costs are that individual catalog rows are 4 bytes
bigger iff wal_level = logical. That seems acceptable.
Some questions remain:
* Better idea for a flag than HEAP_MOVED_OFF | HEAP_MOVED_IN
* Should we just unconditionally log the current CommandId or make it
conditional. We have plenty of flag space to signal whether it's
present, but it's just 4 bytes.
Comments?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical changeset generation v6.4
On 2013-10-18 08:11:29 -0400, Robert Haas wrote: On Mon, Oct 14, 2013 at 9:12 AM, Andres Freund and...@2ndquadrant.com wrote: Attached you can find version 6.4 of the patchset: So I'm still unhappy with the arbitrary logic in what's now patch 1 for choosing the candidate key. On another thread, someone mentioned that they might want the entire old tuple, and that got me thinking: there's no particular reason why the user has to want exactly the columns that exist in some unique, immediate, non-partial index (what a name). So I have two proposals: 1. Instead of allowing the user to choose the index to be used, or picking it for them, how about if we let them choose the old-tuple columns they want logged? This could be a per-column option. If the primary key can be assumed known and unchanging, then the answer might be that the user wants *no* old-tuple columns logged. Contrariwise someone might want everything logged, or anything in the middle. I definitely can see the usecase for logging anything or nothing, arbitrary column select seems to be too complicated for now. 2. If that seems too complicated, how about just logging the whole old tuple for version 1? I think that'd make the patch much less useful because it bloats WAL unnecessarily for the primary user (replication) of it. I'd rather go for primary keys only if that proves to be the contentious point. How about modifying the selection to go from: * all rows if ALTER TABLE ... REPLICA IDENTITY NOTHING|FULL; * index chosen by ALTER TABLE ... REPLICA IDENTITY USING indexname * [later, maybe] ALTER TABLE ... REPLICA IDENTITY (cola, colb) * primary key * candidate key with the smallest oid Including the candidate key will help people using changeset extration for auditing that do not have primary key. That really isn't an infrequent usecase. I've chosen REPLICA IDENTITY; NOTHIN; FULL; because those are all existing keywords, and afaics shouldn't generate any conflicts. On a green field we probably name them differently, but ... Comments? Greetings, Andres Freund PS: candidate key implies a key which is: immediate (aka not deferred), unique, non-partial and only contains NOT NULL columns. -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpgport vs libpgcommon
Peter Eisentraut wrote: On 10/16/13 10:10 PM, Noah Misch wrote: dirmod.c perhaps deserves a split into libpgcommon parts (e.g. pgfnames()) and libpgport parts (e.g. pgrename()). I have also come to this realization. I propose to move pgfnames to src/common/pgfnames.c. Please have a look at my patch at 20130827215416.gf4...@eldon.alvh.no-ip.org particularly the checkdir.c file. Perhaps we'd like to put both these routines (which are related to directories) in a single file (directory.c?). In that case I would suggest putting your new routine in that file, and we'd add the checkdir stuff in there eventually. I don't necessarily object to pgfnames.c in any case, if that's thought to be cleaner. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On 2013-10-18 09:32:15 -0700, Josh Berkus wrote: For that matter, 9.4 is a very good time (relatively speaking) to break replication tools because the new logical replication is going to cause everyone to rev their tools anyway. We're hopefully getting changeset extraction in, but there's little chance of a full blown replication solution making it in in 9.4... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LDAP: bugfix and deprecated OpenLDAP API
Peter Eisentraut wrote: [good suggestions for improvement] I'll send an updated patch on Monday. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On 10/18/2013 12:29 PM, Andres Freund wrote: On 2013-10-18 09:32:15 -0700, Josh Berkus wrote: For that matter, 9.4 is a very good time (relatively speaking) to break replication tools because the new logical replication is going to cause everyone to rev their tools anyway. We're hopefully getting changeset extraction in, but there's little chance of a full blown replication solution making it in in 9.4... I thought changeset extraction was the only thing going into core? What else do we need? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On 2013-10-18 13:16:52 -0700, Josh Berkus wrote: On 10/18/2013 12:29 PM, Andres Freund wrote: On 2013-10-18 09:32:15 -0700, Josh Berkus wrote: For that matter, 9.4 is a very good time (relatively speaking) to break replication tools because the new logical replication is going to cause everyone to rev their tools anyway. We're hopefully getting changeset extraction in, but there's little chance of a full blown replication solution making it in in 9.4... I thought changeset extraction was the only thing going into core? What else do we need? Well, I personally want more in core mid/long term, but anyway. Without released, proven and stable logical in-core replication technology using this, I don't see why repmgr or something related would need/want to change? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: space reserved for WAL record does not match what was written: panic on windows
On Fri, Oct 18, 2013 at 09:05:38PM +1300, David Rowley wrote: As for signed vs unsigned, I've not looked at all of the places where MAXALIGN is used, but I just assumed it was for memory addresses, if this is the case then I'm confused why we'd ever want a negative valued memory address? The result will invariably be cast to a pointer type before use, at which point it's no longer negative. (That's not to say we should keep using signed math, but it doesn't cause active problems for memory addresses.) This might be an obvious one, but can anyone tell me why the casts are in the macro at all? Can a compiler not decide for itself which type it should be using? The casts allow passing values of pointer type, which are not valid as arguments to the bitwise AND operator. -- Noah Misch EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On 10/18/2013 01:35 PM, Andres Freund wrote: On 2013-10-18 13:16:52 -0700, Josh Berkus wrote: I thought changeset extraction was the only thing going into core? What else do we need? Well, I personally want more in core mid/long term, but anyway. I've lost track of the plan, then. Hmmm ... we need replication of DDL commands, no? Without released, proven and stable logical in-core replication technology using this, I don't see why repmgr or something related would need/want to change? Repmgr is designed to manage binary replication, not perform it. What will likely change first is Slony and Bucardo, who have a strong interest in dumping triggers and queues. A contrib module which did the simplest implementation -- that is, whole-database M-S replication -- would also be a good idea, especially since it would provide an example of how to build your own. But I'd be wary of going beyond that in core, because you very quickly get into the territory of trying to satisfy multiple exclusive use-cases. Let's focus on providing a really good API which enables people to build their own tools. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
Tomas, * Tomas Vondra (t...@fuzzy.cz) wrote: My impression from that thread was that one of the requirements is reasonable versioning / diff support, and AFAIK that's not a good match for any GUI-based product. So while I like dia and I used it for drawing the charts I submitted today, I don't think it works with this (quite reasonable) requirement. I'm not sure why you feel that way wrt dia..? As was pointed out in the thread, if you decompress the dia, it's pretty reasonable XML and diffs, etc, will work reasonably well with it. Also, for my part, I'd suggest putting it on the wiki initially anyway, as then it can be seen directly (load it as a png or what-have-you) and it becomes immediately available to users. The .dia should also be on the wiki, of course, and then included in the PG tree eventually if it's added as part of the official docs. No problem with that, but I'd like to know in advance if we're willing to put that into the docs / under what requirements etc. Otherwise it might result in a major effort just to get it from wiki into docs later. I can't see it being a major effort to get it from the wiki into the docs, though perhaps I'm being a bit over-optomistic wrt that. Still, I'd much rather have it somewhere than not have it at all... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Turning recovery.conf into GUCs
On 2013-10-18 14:16:04 -0700, Josh Berkus wrote: On 10/18/2013 01:35 PM, Andres Freund wrote: On 2013-10-18 13:16:52 -0700, Josh Berkus wrote: I thought changeset extraction was the only thing going into core? What else do we need? Well, I personally want more in core mid/long term, but anyway. I've lost track of the plan, then. Hmmm ... we need replication of DDL commands, no? Without released, proven and stable logical in-core replication technology using this, I don't see why repmgr or something related would need/want to change? Repmgr is designed to manage binary replication, not perform it. Obviously. What will likely change first is Slony and Bucardo, who have a strong interest in dumping triggers and queues. But I don't understand what that has to do with recovery.conf and breakage around it. A contrib module which did the simplest implementation -- that is, whole-database M-S replication -- would also be a good idea, especially since it would provide an example of how to build your own. But I'd be wary of going beyond that in core, because you very quickly get into the territory of trying to satisfy multiple exclusive use-cases. Let's focus on providing a really good API which enables people to build their own tools. We'll see. I am certain we'll have many discussions about the bits and pieces you need to build a great replication solution (of which we imo don't have any yet). Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
On 10/18/13 5:35 PM, Stephen Frost wrote: I can't see it being a major effort to get it from the wiki into the docs, though perhaps I'm being a bit over-optomistic wrt that. Hah! Consider that an image would have to work with the following toolchains/output formats: - standalone HTML output - web site HTML output - reasonable mobile/small-screen experience - HTML XSLT - jadetex - pdfjadetex - FOP - Texinfo (at least not break it) - man (at least not break it) - EPUB In my mind, it's not worth the effort unless we have, say, at least a dozen really useful images to add. I don't want to go through this entire pain for one image on a pretty minor topic. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On Fri, Oct 18, 2013 at 11:32 AM, Josh Berkus j...@agliodbs.com wrote: Jaime, well, after upgrade you should do checks. and even if it happens, after it happens once people will be aware of the change. now, some suggestions were made to avoid the problem. 1) read the file if exists last in the process of postgresql.conf, 2) add a GUC indicating if it should read it and include it (not using it as a trigger file). another one, 3) include in this release an include_if_exists directive and give a warning if it sees the file then include it, on next release remove the include_if_exists (at least that way people will be warned before breaking compatibility) I think all of these suggestions just make our code more complicated without improving the upgrade situation. well #3 just add a line in postgresql.conf (an include_if_exists) and current patch gives an error in case it finds the file (i'm suggesting to make it a warning instead). how does that makes our code more complicated? The reason given (and I think it's pretty good) for erroring on recovery.conf is that we don't want people to accidentally take a server out of replication because they didn't check which version of PostgreSQL they are on. well, people will go out of replication also if they forgot the recovery trigger file even if they set the variables in postgresql.conf it happens two me twice today ;) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning recovery.conf into GUCs
On 10/18/2013 02:58 PM, Jaime Casanova wrote: well #3 just add a line in postgresql.conf (an include_if_exists) and current patch gives an error in case it finds the file (i'm suggesting to make it a warning instead). how does that makes our code more complicated? Well, that's a couple extra lines only, I know. However, it doesn't actually help with the breakage any, since recovery.conf *still* won't work as a trigger file. The only thing which would prevent breakage (proposed by Simon, I think) is having recovery.conf have an include_if_exists, AND have recovery.conf be an 'alternate' name for replication.trigger. However, even this would break, and in IMHO ways which would tend to happen at failover time rather than upgrade time. To put it clearly: if we're going to have breakage, I want it to be at upgrade time, when the database is *already down*, and not at failover time or some other time when downtime is not planned. well, people will go out of replication also if they forgot the recovery trigger file even if they set the variables in postgresql.conf it happens two me twice today ;) Right. What I'd like to avoid is having folks try to use, for example, repmgr 1.2 with PostgreSQL 9.4 and have their replication break and them not notice for a couple hours of operation. I'd rather have PostgreSQL 9.4 refuse to come up, so that they know *immediately* that something is wrong. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
* Peter Eisentraut (pete...@gmx.net) wrote: In my mind, it's not worth the effort unless we have, say, at least a dozen really useful images to add. I don't want to go through this entire pain for one image on a pretty minor topic. These are certainly fair issues- my point was merely that once we have support for such in the regular docs, the actual migration from the wiki into the docs would hopefully not be too difficult. As to your point about not wanting to do it for a single image- it seems we could potentially say that for every individual image proposed, but if we don't keep track of those images anywhere then we may not realize that 5 or 10 have actually been done and proposed but never integrated. If they're kept on the wiki then perhaps we would both keep track of the ones proposed and realize when it's worthwhile to add support for them to the doc build system. As to Tom's point on the previous thread, that we would need to actually maintain these images, that is helped by using dia, imv, since it's a pretty simple tool to use and understand and is available on many platforms. There's still some risk there, of course, but it could be worth it in the end. Images really can explain things in a much better way in many cases. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] FDW API / flow charts for the docs?
On 2013-10-18 18:46:10 -0400, Stephen Frost wrote: As to your point about not wanting to do it for a single image- it seems we could potentially say that for every individual image proposed, but if we don't keep track of those images anywhere then we may not realize that 5 or 10 have actually been done and proposed but never integrated. [...] Images really can explain things in a much better way in many cases. +many Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
On 18.10.2013 23:35, Stephen Frost wrote: Tomas, * Tomas Vondra (t...@fuzzy.cz) wrote: My impression from that thread was that one of the requirements is reasonable versioning / diff support, and AFAIK that's not a good match for any GUI-based product. So while I like dia and I used it for drawing the charts I submitted today, I don't think it works with this (quite reasonable) requirement. I'm not sure why you feel that way wrt dia..? As was pointed out in the thread, if you decompress the dia, it's pretty reasonable XML and diffs, etc, will work reasonably well with it. Also, for my part, I'd suggest putting it on the wiki initially anyway, as then it can be seen directly (load it as a png or what-have-you) and it becomes immediately available to users. The .dia should also be on the wiki, of course, and then included in the PG tree eventually if it's added as part of the official docs. No problem with that, but I'd like to know in advance if we're willing to put that into the docs / under what requirements etc. Otherwise it might result in a major effort just to get it from wiki into docs later. I can't see it being a major effort to get it from the wiki into the docs, though perhaps I'm being a bit over-optomistic wrt that. Still, I'd much rather have it somewhere than not have it at all... I meant something a bit different. Imagine I start with a simple chart but spend a lot of time improving it over time (adding comments, formattting) only to find out later I need to redo that from scratch in a different tool. Wouldn't that be easier to start with the target tool in the first place? Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
On 18.10.2013 23:52, Peter Eisentraut wrote: On 10/18/13 5:35 PM, Stephen Frost wrote: I can't see it being a major effort to get it from the wiki into the docs, though perhaps I'm being a bit over-optomistic wrt that. Hah! Consider that an image would have to work with the following toolchains/output formats: - standalone HTML output - web site HTML output - reasonable mobile/small-screen experience - HTML XSLT - jadetex - pdfjadetex - FOP - Texinfo (at least not break it) - man (at least not break it) - EPUB In my mind, it's not worth the effort unless we have, say, at least a dozen really useful images to add. I don't want to go through this entire pain for one image on a pretty minor topic. AFAIK graphviz can give you at least .ps .svg .fig .png .gif .dia formats (and some other). I believe that covers most (if not all) of the cases you've mentioned. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] pg_sleep(interval)
On 10/17/13 12:10 PM, Josh Berkus wrote: On 10/17/2013 10:01 AM, Robert Haas wrote: But if you're asking my opinion, I think doing it on the function level is a whole lot better and easier to get right. A flag like the one I mentioned here can be set for one particular function with the absolute certainty that behavior will not change for any function with some other name. That type of surety is pretty much impossible to get with casts. The other argument for doing it at the function level is that we could then expose it to users, who could use it to manage their own overloaded functions. We would NOT want to encourage users to mess with cast precedence, because it would be impossible for them to achieve their desired result that way. On the other hand, prioritization at the function level likely wouldn't help us with operators at all, because there the cast has to be chosen before we choose a function. So if we pursued the function route, then we'd eventually want to add a preferred flag for operators too. Which would be a lot more trouble, because it would affect the planner, but at least that would be a seperate step. Yeah, but hasn't every case of this that we've run into been directly related to casting problems, and not function or operator preference? ISTM that exposing the idea of function priority to users is opening a massive Pandora's box... Something else I'm wondering is if priority should actually be something that's numbered instead of just a boolean. I can see far more logic to implicitly casting text to double than I can text to interval, but if a cast to double won't actually get you where you want and a cast to interval will... Maybe it's possible to account for all those cases with just a boolean... maybe not. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] pg_sleep(interval)
On 10/17/13 4:01 PM, Vik Fearing wrote: On 10/17/2013 06:59 PM, Josh Berkus wrote: Our project has a serious, chronic problem with giving new patch-submitters a bad experience, and this patch is a good example of that. The ultimate result is that people go off to contribute to other projects where submissions are easier and the rules for what gets accepted are relatively transparent. That may be true, but it depends on the contributor. I would much rather be told that my contribution is not up to snuff than what happened on another project I recently tried to contribute to for the first time. A parser refactoring broke my code. I reported it and it was promptly fixed. When the fix came up for review, I said it needed a regression test to prevent it from happening again and I was told by the author that such a test would be flimsy and it went on to be committed (by that same guy) without one. I'm undecided whether I'll be contributing there any further. The rigor here makes me want to try and try again. ISTM the big issue with new contributors is our methodology is rather different from most other projects, and if you don't understand that you're likely to end up with negativity towards contributing here. Specifically: - We place a heavy, HEAVY emphasis on discussion, to the point that you can easily spend 50x more time on discussing a feature over implementing it. - We place a very heavy emphasis on quality, be that testing, not breaking backwards compatability, etc, etc. I agree with Vik; I think the way we develop is a feature and not a bug. But I also think we need to do everything we can to enlighten new contributors so they don't walk away with a bad taste in their mouth. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multiple psql -c / -f options
On 10/18/13 8:39 AM, Andrew Dunstan wrote: On 10/18/2013 02:19 AM, Fabien COELHO wrote: IMHO the current behavior is broken: decibel@decina:[17:46]~/pgsql/HEAD/i$bin/psql -c 'select 1' -c 'select 2' ?column? -- 2 (1 row) Another try with one -c but with similar results: sh psql -c SELECT 1; SELECT 'hello'; ?column? -- hello (1 row) sh psql -V psql (PostgreSQL) 9.3.1 It's not broken. All this behaviour is documented fairly explicitly. See http://www.postgresql.org/docs/current/static/app-psql.html For example, regarding Fabio's example, which is actually very different from Jim's, the docs say: only the result of the last SQL command is returned. If you want to argue that it should be enhanced, then do. But it's acting as designed and as documented. Perhaps broken was a bad choice of words. :) Even if the owner's manual for your car says You must manually lock the doors before you can start the engine that doesn't mean it's good behavior. ;) There's actually additional problems with compound statements. For example, EXECUTE 'CREATE TABLE foo(...); ALTER TABLE foo ...;' doesn't work (at least last I checked). I ass-u-me that there's some fundamental issue to fixing that, so I haven't even looked into it. When it comes to multiple command-line options, ISTM that current behavior fails the least surprise test miserably by simply ignoring some options: psql --cluster 9.1/us-cnuapp_b -d cnuapp_prod -c 'CREATE TEMP VIEW t AS SELECT 1' -c 'SELECT * FROM t' ERROR: relation t does not exist LINE 1: SELECT * FROM t I've never run across any other command-line tool that does that, and I don't think we should either. I suspect changing this might actually have more wrinkles that you imagine, but I could be wrong. The only one I've thought of is some users might actually be depending on existing behavior... Incidentally, both of you could probably achieve what you apparently want with: echo 'some sql here' | psql True... while I personally think it'd be nice to actually support multiple -c/-f options it's not all that hard to work around that being missing. What does concern me is that we're intentionally ignoring requests the user has made of psql. We should either fulfill the requests or throw an error. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW API / flow charts for the docs?
I would be more inclined to let GraphViz into the process than Dia; the former fits *much* better into a Make-based process. It is worth observing that there are schema diagramming systems (SchemaSpy is mighty likable) that build diagrams using GraphViz. We have integrated this into internal deployments at Afilias. Rod Taylor's autodoc tool has some minimal Dia support, but it lacks the ability to do layout; if you use Dia output, you'll have to manually drag things around to have the tables *not* all atop one another. I'm a little surprised Dia hasn't integrated in GraphViz to help with that; note that the MacOS diagram tool OmniGraffle (proprietary, considered quite good, competitive with Visio) uses GraphViz to help do automatic object layout. That this is integrated inside an interactive GUI app is pretty wild/cool. I'd think it a fine idea to add rules for .dot files (the usual GraphViz language/format to describe graphs) to the PG Make rules.
Re: [HACKERS] libpgport vs libpgcommon
On Fri, 2013-10-18 at 16:00 -0300, Alvaro Herrera wrote: Please have a look at my patch at 20130827215416.gf4...@eldon.alvh.no-ip.org particularly the checkdir.c file. Perhaps we'd like to put both these routines (which are related to directories) in a single file (directory.c?). In that case I would suggest putting your new routine in that file, and we'd add the checkdir stuff in there eventually. I think smaller files are better, especially for a static library. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals
On Thu, 2013-10-17 at 13:49 +1100, James Sewell wrote: The search+bind mode issue is one of documentation location, I have fixed it by moving the section to the applied to both list. As the patch is to do with post-auth response this is correct. Makes sense. As far as the issue when something other than 0 or 1 is set I am happy throw an error (although this doesn't seem to be how option such as LDAPTLS work: 1 if 1 else 0). Right, that's how ldapreferrals ought to work as well. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR : 'tuple concurrently updated'
On Fri, Oct 18, 2013 at 3:43 PM, Stéphan BEUZE
stephan.be...@douane.finances.gouv.fr wrote:
Here I provide more details about the environment where the error occurs:
* ENVIRONMENT
Client:
Java Web Application running on JBoss 5.0.0.GA - JDK 1.6.0_24 64bit
Server:
Postgresql 9.2.4, compiled by Visual C++ build 1600, 64bit
Client and Server run on the same platform:
Windows 7 Professional SP1 (2009)
* STRUCTURES
CREATE ROLE rec LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
CREATE ROLE rec_lct LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE
NOREPLICATION;
CREATE SCHEMA rec AUTHORIZATION rec;
GRANT ALL ON SCHEMA rec TO rec;
GRANT USAGE ON SCHEMA rec TO rec_lct;
ALTER ROLE rec SET search_path = rec;
ALTER ROLE rec_lct SET search_path = rec;
SET SCHEMA 'rec'
CREATE SEQUENCE stats_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 120
CYCLE;
ALTER TABLE stats_sequence OWNER TO rec;
GRANT ALL ON TABLE stats_sequence TO rec;
GRANT UPDATE ON TABLE stats_sequence TO rec_lct;
CREATE TABLE my_stat
(
id bigint NOT NULL,
creation date NOT NULL DEFAULT current_date,
client_addr text NOT NULL,
pid integer NOT NULL,
usename name NOT NULL,
CONSTRAINT my_stat _pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE statistiques_connexions OWNER TO rec;
GRANT ALL ON TABLE statistiques_connexions TO rec;
GRANT SELECT, INSERT ON TABLE statistiques_connexions TO rec_lct;
Is this table statistiques_connexions used for something different
from my_stat or this is actual name of my_stat used in your
application?
CREATE INDEX statistiques_connexions_idx_creation
ON statistiques_connexions
USING btree
(creation);
CREATE INDEX statistiques_connexions_idx_ukey
ON statistiques_connexions
USING btree
(creation, pid, client_addr COLLATE pg_catalog.default, usename);
* CONTEXT
Two Java threads are created. One is connected with 'rec' user, while the
other one
is connected with 'rec_lct' user.
The threads don't create themselves their JDBC connections.
Instead, they each have their own pooled datasource preconfigured.
The pooled datasources are managed by the same connection pool
library: c3p0 0.9.1. The pooled datasources each open 3 connections
on startup. They can make this number of connections variate from 1 to 5
connections.
In our development context, this number of connections stay at 3.
The threads run the following query every 500 ms.
With the above information, it is difficult to imagine the cause of
problem, is it possible for you to write a separate test which you can
post here, if you can write using some scripts or libpq, that would
also be sufficient.
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
FROM
pg_stat_activity
WHERE
usename = current_user
)
INSERT INTO my_stat(id, client_addr, pid, usename)
SELECT
nextval('mystat_sequence'), t.client_addr, t.pid, t.usename
FROM (
SELECT
client_addr, pid, usename
FROM
raw_stat s
WHERE
NOT EXISTS (
SELECT
NULL
FROM
my_stat u
WHERE
current_date = u.creation
AND
s.pid = u.pid
AND
s.client_addr = u.client_addr
AND
s.usename = u.usename
)
) t;
What can be observed first is that, at the beginning, everything run
smoothly.
Then unpredictably, the error 'tuple concurrently updated' appears...
Needless to say, that it disappears too... unpredictably.
Sometimes, it can shows up contisnously.
Do you see any other problem due to this error in your database?
Tell me if you need some more detailed information.
Stephan
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch to compute Max LSN of Data Pages
On Fri, Oct 18, 2013 at 9:01 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Oct 9, 2013 at 2:28 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Sep 14, 2013 at 3:03 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Monday, July 08, 2013 5:16 PM Andres Freund wrote: On 2013-07-08 17:10:43 +0530, Amit Kapila wrote: On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: I am OK with this patch in its current form, modulo some grammar issues in the documentation which I can fix before committing. However, I'm unclear whether there was sufficient consensus to proceed with this. Can others weigh in? If there is too much residual unhappiness with this, then we should just mark this as Rejected and stop wasting time on it; it can be pushed to PGXN or similar even if we don't put it in core. I didn't hear any other votes. Anyone else have an opinion about this? If I can't get a +1 from anyone who wasn't involved in writing the patch, I'm inclined to think we don't have sufficient consensus to commit this. On further review of the patch, I also found a number of other issues that I think need to be fixed before we could consider committing it: - Per a previous request of mine, the patch has three different modes: it can be run on an individual file, on a directory potentially containing multiple relation files, or on an entire data directory. This is not explained in the documentation. There is some explanation about it, but I think you want to see in different format or wording. I will change it to explain it more clearly in next update of this patch. + titleDescription/title + para + commandpg_computemaxlsn/command computes maximun LSN from database pages + in the specified list of files or directories. + /para + + para + If user doesn't provide the file or directory to find the max lsn then + commandpg_computemaxlsn/command use the environment variable envarPGDATA/ + if exists otherwise reports an error. + /para - The patch skips printing an error if attempting to open a file returns ENOENT. I don't see why that case shouldn't print an error. Yeah, it could be legit if you're executing this against a running server, but why are you doing that? And even if you are (e.g. for corruption detection), printing a error message and proceeding makes more sense than proceeding without printing anything, at least IMHO. - Some of the static functions in this file preceed main and others follow it. And they use different naming conventions. I suggest putting all of them after main() and using names like check_data_dir, check_data_file_name (instead of validateRelfilenodename; note that the comment for that function is also incorrect), find_max_lsn_in_file, find_max_lsn_in_directory, find_max_lsn_in_pgdata. - Since the patch goes to some pains to exit with 0 if no errors were encountered and 1 if any were, that probably ought to be documented. But I think instead of passing a result argument back up the call stack it would be better to just keep a global variable called errors_encountered. When we encounter an error, bump that value. Then you can just do exit(errors_encountered 0 ? 1 : 0) and not bother passing all of this stuff around via the return value. The current behavior is inconsistent in another way, too: if you encounter an error while scanning through a particular directory, you finish the whole directory. But if multiple command-line arguments were passed, you don't proceed to any subsequent command-line arguments. I think you should continue always, which the above-mentioned change will take care of basically for free. - The description of the -q option is not very clear. A reader could be forgiven for thinking that the option suppresses all output, which would be quite useless, or at least left in doubt about what output will still be provided. Thank you for your feedback. I will update it in next version of patch if there is a consensus to proceed for this utility. A broader complaint I have with this patch is that it almost but not-quite solves a problem I've had a few times in the past: namely, searching through the data directory for data blocks which have LSNs in the future. This has come up a few times for me, and this tool would make it easier, because I'd be able to run it and look through the output to see which relations have high max-LSN values. However, it wouldn't be quite enough, because it'd only tell me about the block with the highest LSN in each file, whereas what I'd really want to find is every block with an LSN greater than some threshold value. Maybe I'm pushing the envelope too much by trying to fit that into the framework of this patch, but I can't help thinking we're not going to want both pg_computemaxlsn and pg_findlsnsaftersomethreshold that are 95% the same code, so maybe we ought to rename the utility to
Re: [HACKERS] Review: Patch to compute Max LSN of Data Pages
On Fri, Oct 18, 2013 at 9:24 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas escribió: A broader complaint I have with this patch is that it almost but not-quite solves a problem I've had a few times in the past: namely, searching through the data directory for data blocks which have LSNs in the future. This has come up a few times for me, and this tool would make it easier, because I'd be able to run it and look through the output to see which relations have high max-LSN values. However, it wouldn't be quite enough, because it'd only tell me about the block with the highest LSN in each file, whereas what I'd really want to find is every block with an LSN greater than some threshold value. Maybe I'm pushing the envelope too much by trying to fit that into the framework of this patch, but I can't help thinking we're not going to want both pg_computemaxlsn and pg_findlsnsaftersomethreshold that are 95% the same code, so maybe we ought to rename the utility to something slightly more generic than pg_computemaxlsn. Perhaps not coincidentally, I had a need to do this recently. Perhaps we should turn the utility into a generic tool to report existing LSNs, with options to 1) report only the highest one in a given file, 2) report only those that exceed some threshold. So maybe pg_reportlsn or pg_extractlsn. How about extending it validate database in more meaningful way and name it as validatedb. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compression of full-page-writes
On Tue, Oct 15, 2013 at 11:41 AM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: (2013/10/15 13:33), Amit Kapila wrote: Snappy is good mainly for un-compressible data, see the link below: http://www.postgresql.org/message-id/CAAZKuFZCOCHsswQM60ioDO_hk12tA7OG3YcJA8v=4yebmoa...@mail.gmail.com This result was gotten in ARM architecture, it is not general CPU. Please see detail document. http://www.reddit.com/r/programming/comments/1aim6s/lz4_extremely_fast_compression_algorithm/c8y0ew9 I think in general also snappy is mostly preferred for it's low CPU usage not for compression, but overall my vote is also for snappy. I found compression algorithm test in HBase. I don't read detail, but it indicates snnapy algorithm gets best performance. http://blog.erdemagaoglu.com/post/4605524309/lzo-vs-snappy-vs-lzf-vs-zlib-a-comparison-of The dataset used for performance is quite different from the data which we are talking about here (WAL). These are the scores for a data which consist of 700kB rows, each containing a binary image data. They probably won’t apply to things like numeric or text data. In fact, most of modern NoSQL storages use snappy. Because it has good performance and good licence(BSD license). I think it is bit difficult to prove that any one algorithm is best for all kind of loads. I think it is necessary to make best efforts in community than I do the best choice with strict test. Sure, it is good to make effort to select the best algorithm, but if you are combining this patch with inclusion of new compression algorithm in PG, it can only make the patch to take much longer time. In general, my thinking is that we should prefer compression to reduce IO (WAL volume), because reducing WAL volume has other benefits as well like sending it to subscriber nodes. I think it will help cases where due to less n/w bandwidth, the disk allocated for WAL becomes full due to high traffic on master and then users need some alternative methods to handle such situations. I think many users would like to use a method which can reduce WAL volume and the users which don't find it enough useful in their environments due to decrease in TPS or not significant reduction in WAL have the option to disable it. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
