Re: [HACKERS] xlog filename formatting functions in recovery
On 03.07.2012 15:13, Robert Haas wrote:
On the substance of the patch, I believe the reason why this is
currently disallowed is because the TLI is implicitly taken from the
running system, and on the standby that might be the wrong value.
Yeah, I believe that's the reason. So the question is, what timeline
should the functions use on a standby? With the patch as it is, they use 0:
postgres=# select pg_xlogfile_name_offset('3/FF02');
pg_xlogfile_name_offset
---
(000300FF,131072)
(1 row)
There's a few different options:
1. current recovery_target_timeline (XLogCtl-recoveryTargetTLI)
2. current ThisTimeLineID, which is bumped every time a timeline-bumping
checkpoint record is replayed. (this is not currently visible to
backends, but we could easily add a shared memory variable for it)
3. curFileTLI. That is, the TLI of the current file that we're
replaying. This is usually the same as ThisTimeLineID, except when
replaying a WAL segment where the timeline changes
4. Something else?
What do you use these functions for? Which option would make the most sense?
- Heikki
--
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] 64-bit API for large object
I think Tom's point is, there are tons of applications which define their own int64_t (at least in 2005). Also pg_config.h has: #define HAVE_STDINT_H 1 and this suggests that PostgreSQL adopts to platforms which does not have stdint.h. If so, we need to take care of such platforms anyway. OK, it makes me clear. It might be helpful a source code comment to remain why we used self defined datatype here. 2012/9/21 Tom Lane t...@sss.pgh.pa.us: Tatsuo Ishii is...@postgresql.org writes: To pass 64-bit integer to PQfn, PQArgBlock is used like this: int *ptr is a pointer to 64-bit integer and actual data is placed somewhere else. Yeah, I think we have to do it like that. Changing the size of PQArgBlock would be a libpq ABI break, which IMO is sufficiently painful to kill this whole proposal. Much better a little localized ugliness in fe-lobj.c. Hmm, I see. Please deliver the 64bit integer argument as reference, and don't forget endian translations here. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] 64-bit API for large object
Currently lo_initialize() throws an error if one of oids are not available. I doubt we do the same way for 64-bit functions since this will make 9.3 libpq unable to access large objects stored in pre-9.2 PostgreSQL servers. It seems to me the situation to split the case of pre-9.2 and post-9.3 using a condition of conn-sversion = 90300. Agreed. I'll fix it like that. 4) src/test/examples/testlo64.c added for 64-bit API example(Yugo Nagata) Comments and suggestions are welcome. miscellaneous comments are below. Regression test is helpful. Even though no need to try to create 4TB large object, it is helpful to write some chunks around the design boundary. Could you add some test cases that writes some chunks around 4TB offset. Agreed. I'll do that. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Yugo Nagata nag...@sraoss.co.jp -- 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] [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
于 2012/9/19 7:22, Bruce Momjian 写道:
On Mon, Sep 17, 2012 at 05:07:23PM -0400, Bruce Momjian wrote:
# select * from pg_tables where tablename='sql_features';
schemaname | tablename | tableowner | tablespace |
hasindexes | hasrules | hastriggers
+--++++--+-
information_schema | sql_features | postgres || f
| f| f
(1 row)
OK, good to know. This is the query pg_upgrade 9.2 uses to pull
information from 9.1 and 9.2:
SELECT c.oid, n.nspname, c.relname, c.relfilenode, c.reltablespace,
t.spclocation
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON
c.relnamespace = n.oid
LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace =
t.oid
WHERE relkind IN ('r','t', 'i', 'S') AND
((n.nspname !~ '^pg_temp_' AND
n.nspname !~ '^pg_toast_temp_' AND
n.nspname NOT IN ('pg_catalog', 'information_schema',
'binary_upgrade') AND
c.oid = 16384
)
OR
(n.nspname = 'pg_catalog' AND
relname IN
('pg_largeobject', 'pg_largeobject_loid_pn_index',
'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index')
)
)
ORDER BY 1;
Based on the fact that sql_features exists in the information_schema
schema, I don't think 'sql_features' table is actually being processed
by pg_upgrade, but I think its TOAST table, because it has a high oid,
is being processed because it is in the pg_toast schema. This is
causing the mismatch between the old and new clusters.
I am thinking this query needs to be split apart into a UNION where the
second part handles TOAST tables and looks at the schema of the _owner_
of the TOAST table. Needs to be backpatched too.
OK, I am at a conference now so will not be able to write-up a patch
until perhaps next week. You can drop the information schema in the old
database and pg_upgrade should run fine. I will test your failure once
I create a patch.
OK. I will try. I also found some problems on initdb when re-init my
pg9.2 db.
1. initdb doesn't create the pg_log dir so pg can not be started after
initdb before I create the dir manually.
2. The case issue of db charset name. I installed pg9.1 and pg9.2 with
zh_CN.UTF8. But somehow it seems the actual chaset name is stored with
lowercase 'zh_CN.utf8' during the install. In this case, I can run the
pg_upgrade without problem since they are both lowercase. But when I
re-init pg9.2 with option '-E zh_CN.UTF8', pg_upgrade will fail and
report that encoding/charset mis-match: one is uppercase and another is
lowercase. If I run initdb with '-E zh_CN.utf8', it will tell me there
is no such charset in the system. I found a workaround to run initdb
with '--lc-collate=zh_CN.utf8 --lc-ctype=zh_CN.utf8
--lc-messages=zh_CN.utf8 --lc-monetary=zh_CN.utf8
--lc-numeric=zh_CN.utf8 --lc-time=zh_CN.utf8'. But the case problem is
really confusing.
--
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] 64-bit API for large object
I think Tom's point is, there are tons of applications which define their own int64_t (at least in 2005). Also pg_config.h has: #define HAVE_STDINT_H 1 and this suggests that PostgreSQL adopts to platforms which does not have stdint.h. If so, we need to take care of such platforms anyway. OK, it makes me clear. It might be helpful a source code comment to remain why we used self defined datatype here. Ok. 2012/9/21 Tom Lane t...@sss.pgh.pa.us: Tatsuo Ishii is...@postgresql.org writes: To pass 64-bit integer to PQfn, PQArgBlock is used like this: int *ptr is a pointer to 64-bit integer and actual data is placed somewhere else. Yeah, I think we have to do it like that. Changing the size of PQArgBlock would be a libpq ABI break, which IMO is sufficiently painful to kill this whole proposal. Much better a little localized ugliness in fe-lobj.c. Hmm, I see. Please deliver the 64bit integer argument as reference, and don't forget endian translations here. I thought pgPutInt64() takes care of endianness. No? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] 64-bit API for large object
2012/9/21 Tatsuo Ishii is...@postgresql.org: I think Tom's point is, there are tons of applications which define their own int64_t (at least in 2005). Also pg_config.h has: #define HAVE_STDINT_H 1 and this suggests that PostgreSQL adopts to platforms which does not have stdint.h. If so, we need to take care of such platforms anyway. OK, it makes me clear. It might be helpful a source code comment to remain why we used self defined datatype here. Ok. 2012/9/21 Tom Lane t...@sss.pgh.pa.us: Tatsuo Ishii is...@postgresql.org writes: To pass 64-bit integer to PQfn, PQArgBlock is used like this: int *ptr is a pointer to 64-bit integer and actual data is placed somewhere else. Yeah, I think we have to do it like that. Changing the size of PQArgBlock would be a libpq ABI break, which IMO is sufficiently painful to kill this whole proposal. Much better a little localized ugliness in fe-lobj.c. Hmm, I see. Please deliver the 64bit integer argument as reference, and don't forget endian translations here. I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] ToDo: allow to get a number of processed rows by COPY statement [Review of Patch]
On Friday, September 21, 2012 1:23 AM Pavel Stehule wrote:
Basic stuff:
- Patch applies OK. but offset difference in line numbers.
- Compiles with errors in contrib [pg_stat_statements, sepgsql] modules
- Regression failed; one test-case in COPY due to incomplete test-case
attached patch. – same as reported by Heikki
fixed patch is in attachment
After modifications:
---
- Patch applies OK
- Compiles cleanly without any errors/warnings
- Regression tests pass.
What it does:
--
Modification to get the number of processed rows evaluated via SPI. The
changes are to add extra parameter in ProcessUtility to get the number of
rows processed by COPY command.
Code Review Comments:
-
1. New parameter is added to ProcessUtility_hook_type function
but the functions which get assigned to these functions like
sepgsql_utility_command, pgss_ProcessUtility, prototype definition is
not modified.
Functionality is not fixed correctly for hook functions, In function
pgss_ProcessUtility
for bellow snippet of code processed parameter is passed NULL, as well as not
initialized.
because of this when pg_stat_statements extention is utilized COPY command is
giving garbage values.
if (prev_ProcessUtility)
prev_ProcessUtility(parsetree, queryString, params,
dest, completionTag,
context, NULL);
else
standard_ProcessUtility(parsetree, queryString, params,
dest, completionTag,
context, NULL);
Testcase is attached.
In this testcase table has only 1000 records but it show garbage value.
postgres=# show shared_preload_libraries ;
shared_preload_libraries
--
pg_stat_statements
(1 row)
postgres=# CREATE TABLE tbl (a int);
CREATE TABLE
postgres=# INSERT INTO tbl VALUES(generate_series(1,1000));
INSERT 0 1000
postgres=# do $$
declare r int;
begin
copy tbl to '/home/kiran/copytest.csv' csv;
get diagnostics r = row_count;
raise notice 'exported % rows', r;
truncate tbl;
copy tbl from '/home/kiran/copytest.csv' csv;
get diagnostics r = row_count;
raise notice 'imported % rows', r;
end;
$$ language plpgsql;
postgres$#
NOTICE: exported 13281616 rows
NOTICE: imported 13281616 rows
DO
2. Why to add the new parameter if completionTag hold the number of
processed tuple information; can be extracted
from it as follows:
_SPI_current-processed = strtoul(completionTag + 7,
NULL, 10);
this is basic question. I prefer a natural type for counter - uint64
instead text. And there are no simply way to get offset (7 in this
case)
I agree with your point, but currently in few other places we are parsing
the completion tag for getting number of tuples processed. So may be in future
we can change those places as well. For example
pgss_ProcessUtility
{
..
/* parse command tag to retrieve the number of affected rows. */
if (completionTag
sscanf(completionTag, COPY UINT64_FORMAT, rows) != 1)
rows = 0;
}
_SPI_execute_plan
{
..
..
if (IsA(stmt, CreateTableAsStmt))
{
Assert(strncmp(completionTag, SELECT , 7) == 0);
_SPI_current-processed = strtoul(completionTag + 7,
NULL,
10);
..
}
With Regards,
Amit Kapila.
--
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] 64-bit API for large object
Hmm, I see. Please deliver the 64bit integer argument as reference, and don't forget endian translations here. I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. I think we cannot change this because we want to keep the counter part backend side function pq_getmsgint64() as it is (the function is not part of the patch). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] 64-bit API for large object
2012/9/21 Tatsuo Ishii is...@postgresql.org: Hmm, I see. Please deliver the 64bit integer argument as reference, and don't forget endian translations here. I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. I think we cannot change this because we want to keep the counter part backend side function pq_getmsgint64() as it is (the function is not part of the patch). My opinion is lo_lseek64() and lo_tell64() should handle endian translation prior and next to PQfn() invocation; to avoid the int64 specific case-handling inside of PQfn() that can be called by other applications. Am I missing something? Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] 64-bit API for large object
I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. I think we cannot change this because we want to keep the counter part backend side function pq_getmsgint64() as it is (the function is not part of the patch). My opinion is lo_lseek64() and lo_tell64() should handle endian translation prior and next to PQfn() invocation; to avoid the int64 specific case-handling inside of PQfn() that can be called by other applications. Am I missing something? So what do you want to do with pq_getmsgint64()? It exactly does the same thing as pqPutInt64(), just in opposit direction. Do you want to change pq_getmsgint64()? Or add new function in backend? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] 64-bit API for large object
2012/9/21 Tatsuo Ishii is...@postgresql.org: I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. I think we cannot change this because we want to keep the counter part backend side function pq_getmsgint64() as it is (the function is not part of the patch). My opinion is lo_lseek64() and lo_tell64() should handle endian translation prior and next to PQfn() invocation; to avoid the int64 specific case-handling inside of PQfn() that can be called by other applications. Am I missing something? So what do you want to do with pq_getmsgint64()? It exactly does the same thing as pqPutInt64(), just in opposit direction. Do you want to change pq_getmsgint64()? Or add new function in backend? My preference is nothing are changed both pg_getmsgint64() of the backend and routines under PQfn() of the libpq. Isn't it unavailable to deliver int64- value after the endian translation on the caller side? Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] 64-bit API for large object
2012/9/21 Tatsuo Ishii is...@postgresql.org: I thought pgPutInt64() takes care of endianness. No? It works inside of the PGfn(), when isint = 1 towards pointer data type. In my sense, it is a bit problem specific solution. So, I'd like to see other person's opinion here. I think we cannot change this because we want to keep the counter part backend side function pq_getmsgint64() as it is (the function is not part of the patch). My opinion is lo_lseek64() and lo_tell64() should handle endian translation prior and next to PQfn() invocation; to avoid the int64 specific case-handling inside of PQfn() that can be called by other applications. Am I missing something? So what do you want to do with pq_getmsgint64()? It exactly does the same thing as pqPutInt64(), just in opposit direction. Do you want to change pq_getmsgint64()? Or add new function in backend? My preference is nothing are changed both pg_getmsgint64() of the backend and routines under PQfn() of the libpq. Isn't it unavailable to deliver int64- value after the endian translation on the caller side? I am confused. My opinion is lo_lseek64() and lo_tell64() should handle endian translation prior and next to PQfn() invocation; to avoid the int64 specific case-handling inside of PQfn() that can be called by other applications. Why do we need this? If PQArgBlock.isint != 0, it treats input data as integer anyway. So I don't see any use case other than int64 specific case-handling if isint != 0 and len == 8. If you have other use case for isint != 0 and len == 8, please show it. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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: [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Tuesday, September 18, 2012 6:02 PM Fujii Masao wrote: On Mon, Sep 17, 2012 at 4:03 PM, Amit Kapila amit.kap...@huawei.com wrote: Approach-2 : Provide a variable wal_send_status_interval, such that if this is 0, then the current behavior would prevail and if its non-zero then KeepAlive message would be send maximum after that time. The modified code of WALSendLoop will be as follows: snip Which way you think is better or you have any other idea to handle. I think #2 is better because it's more intuitive to a user. Please find a patch attached for implementation of Approach-2. With Regards, Amit Kapila. replication_timeout_patch_v2.patch Description: replication_timeout_patch_v2.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] pg_reorg in core?
On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt schmi...@gmail.com wrote: If the argument for moving pg_reorg into core is faster and easier development, well I don't really buy that. I don't see any problem in having pg_reorg in PGXN instead. I've tried adding a META.json to the project and it seems working fine with the pgxn client. It is together with other patches in my own github fork. https://github.com/dvarrazzo/pg_reorg/ I haven't submitted it to PGXN as I prefer the original author to keep the ownership. -- Daniele -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [v9.3] Extra Daemons (Re: [HACKERS] elegant and effective way for running jobs inside a database)
Excerpts from Amit Kapila's message of vie sep 21 02:26:49 -0300 2012: On Thursday, September 20, 2012 7:13 PM Alvaro Herrera wrote: Well, there is a difficulty here which is that the number of processes connected to databases must be configured during postmaster start (because it determines the size of certain shared memory structs). So you cannot just spawn more tasks if all max_worker_tasks are busy. (This is a problem only for those workers that want to be connected as backends. Those that want libpq connections do not need this and are easier to handle.) Are you telling about shared memory structs that need to be allocated for each worker task? I am not sure if they can be shared across multiple slaves or will be required for each slave. However even if that is not possible, other mechanism can be used to get the work done by existing slaves. I mean stuff like PGPROC entries and such. Currently, they are allocated based on max_autovacuum_workers + max_connections + max_prepared_transactions IIRC. So by following identical reasoning we would just have to add an hypothetical new max_bgworkers to the mix; however as I said above, we don't really need that because we can count the number of registered workers at postmaster start time and use that to size PGPROC. Shared memory used by each worker (or by a group of workers) that's not part of core structs should be allocated by the worker itself via RequestAddInShmemSpace. If not above then where there is a need of dynamic worker tasks as mentioned by Simon? Well, I think there are many uses for dynamic workers, or short-lived workers (start, do one thing, stop and not be restarted). In my design, a worker is always restarted if it stops; otherwise there is no principled way to know whether it should be running or not (after a crash, should we restart a registered worker? We don't know whether it stopped before the crash.) So it seems to me that at least for this first shot we should consider workers as processes that are going to be always running as long as postmaster is alive. On a crash, if they have a backend connection, they are stopped and then restarted. One thing I am not going to look into is how is this new capability be used for parallel query. I feel we have enough use cases without it, that we can develop a fairly powerful feature. After that is done and proven (and committed) we can look into how we can use this to implement these short-lived workers for stuff such as parallel query. Agreed and I also meant to say the same as you are saying. Great. -- Á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] [COMMITTERS] pgsql: Properly set relpersistence for fake relcache entries.
On 9/20/12 11:55 PM, Andres Freund wrote: On Monday, September 17, 2012 03:58:37 PM Tom Lane wrote: OK, that explains why we've not seen a blizzard of trouble reports. Still seems like a good idea to fix it ASAP, though. Btw, I think RhodiumToad/Andrew Gierth and I some time ago helped a user in the IRC Channel that had symptoms matching this bug. Another such user reporting in. :-( Our slave started accumulating WAL files and ran out of disk space yesterday. After investigation from Andres and Andrew, it turns out that we were most likely hit by this very same bug. Here's what they have to say: If the db crashes between logging the split and the parent-node insert, then in recovery, since relpersistence is not initialized correctly, when the recovery process tries to complete the operation, no xlog record is written for the insert. If there's a slave server, then the missing xlog record for the insert means that the slave's incomplete_actions queue never becomes empty, therefore the slave can no longer do recovery restartpoints. Some relevant information: [cur:92/314BC870, xid:76872047, rmid:10(Heap), len/tot_len:91/123, info:0, prev:92/314BB890] insert: s/d/r:1663/408841/415746 blk/off:13904/65 header: t_infomask2 8 t_infomask 2050 t_hoff 24 [cur:92/314BC8F0, xid:76872047, rmid:11(Btree), len/tot_len:702/734, info:64, prev:92/314BC870] split_r: s/d/r:1663/408841/475676 leftsib 2896 [cur:92/314BCBD0, xid:0, rmid:0(XLOG), len/tot_len:56/88, info:0, prev:92/314BC8F0] checkpoint: redo 146/314BCBD0; tli 1; nextxid 76872048; nextoid 764990; nextmulti 62062; nextoffset 132044; shutdown at 2012-09-11 14:26:26 CEST 2012-09-11 14:26:26.719 CEST,,,44620,,504f2df2.ae4c,5,,2012-09-11 14:26:26 CEST,,0,LOG,0,redo done at 92/314BC8F0StartupXLOG, xlog.c:6641, And apparently the relpersistence check in RelationNeedsWAL() call in _bt_insertonpg had a role in this as well. Regards, Marko Tiikkaja -- 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] pg_reorg in core?
On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo daniele.varra...@gmail.com wrote: On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt schmi...@gmail.com wrote: If the argument for moving pg_reorg into core is faster and easier development, well I don't really buy that. I don't see any problem in having pg_reorg in PGXN instead. I've tried adding a META.json to the project and it seems working fine with the pgxn client. It is together with other patches in my own github fork. https://github.com/dvarrazzo/pg_reorg/ I haven't submitted it to PGXN as I prefer the original author to keep the ownership. Thanks, I merged your patches with the dev branch for the time being. It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn. -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] pg_reorg in core?
On Fri, Sep 21, 2012 at 1:00 PM, Hitoshi Harada umi.tan...@gmail.comwrote: I'm not familiar with pg_reorg, but I wonder why we need a separate program for this task. I know pg_reorg is ok as an external program per se, but if we could optimize CLUSTER (or VACUUM which I'm a little pessimistic about) in the same way, it's much nicer than having additional binary + extension. Isn't it possible to do the same thing above within the CLUSTER command? Maybe CLUSTER .. CONCURRENTLY? CLUSTER might be more adapted in this case as the purpose is to reorder the table. The same technique used by pg_reorg (aka table coupled with triggers) could lower the lock access of the table. Also, it could be possible to control each sub-operation in the same fashion way as CREATE INDEX CONCURRENTLY. By the way, whatever the operation, VACUUM or CLUSTER used, I got a couple of doubts: 1) isn't it be too costly for a core operation as pg_reorg really needs many temporary objects? Could be possible to reduce the number of objects created if added to core though... 2) Do you think the current CLUSTER is enough and are there wishes to implement such an optimization directly in core? -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] 64-bit API for large object
Kohei KaiGai kai...@kaigai.gr.jp writes: My preference is nothing are changed both pg_getmsgint64() of the backend and routines under PQfn() of the libpq. Isn't it unavailable to deliver int64- value after the endian translation on the caller side? Right. If we had to change anything on the backend side, it would mean we had a wire protocol change, which is even less acceptable than a libpq ABI change. 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] [COMMITTERS] pgsql: Properly set relpersistence for fake relcache entries.
On Friday, September 21, 2012 03:30:31 PM Marko Tiikkaja wrote: On 9/20/12 11:55 PM, Andres Freund wrote: On Monday, September 17, 2012 03:58:37 PM Tom Lane wrote: OK, that explains why we've not seen a blizzard of trouble reports. Still seems like a good idea to fix it ASAP, though. Btw, I think RhodiumToad/Andrew Gierth and I some time ago helped a user in the IRC Channel that had symptoms matching this bug. Another such user reporting in. :-( Our slave started accumulating WAL files and ran out of disk space yesterday. After investigation from Andres and Andrew, it turns out that we were most likely hit by this very same bug. Here's what they have to say: If the db crashes between logging the split and the parent-node insert, then in recovery, since relpersistence is not initialized correctly, when the recovery process tries to complete the operation, no xlog record is written for the insert. If there's a slave server, then the missing xlog record for the insert means that the slave's incomplete_actions queue never becomes empty, therefore the slave can no longer do recovery restartpoints. Some relevant information: [cur:92/314BC870, xid:76872047, rmid:10(Heap), ... insert: ... [cur:92/314BC8F0, xid:76872047, rmid:11(Btree), ... split_r: ... [cur:92/314BCBD0, xid:0, rmid:0(XLOG), len/tot_len:56/88, info:0, prev:92/314BC8F0] checkpoint: redo 146/314BCBD0; ... shutdown ... redo done at 92/314BC8F0StartupXLOG, xlog.c:6641, Which means that an insert into the heap, triggered a btree split. At that point the database crashed. During recovery the split was supposed to be finished by the btree cleanup code. And apparently the relpersistence check in RelationNeedsWAL() call in _bt_insertonpg had a role in this as well. When detecting an incomplete split the nbtree cleanup code calls _bt_insert_parent, which calls _bt_insertonpg. Which finishes the split. BUT: it doesn't log that it finished because RelationNeedsWal() says it doesn't need to. That means: * indexes on stanbys will *definitely* be corrupted * a standby won't perform any restartpoints anymore till restarted * if the primary crashes corruption is likely. Hrm. I retract my earlier statement about the low likelihood of corruption due to this. Greetings, Andres -- 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] DB Schema
During the execution of ProcessUtility() function in /src/backend/xxx/utility.c, the CreateStmt node type is processed to create a table. Is there a global function in the context of the backend process that will deliver what the current database and schema names? The querystring cannot be relied upon for discerning this information. marco -- 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: [WIP] Patch : Change pg_ident.conf parsing to be the same as pg_hba.conf
On 02.07.2012 15:08, Amit Kapila wrote: Attached is a Patch to change the parsing of pg_ident.conf to make it similar to pg_hba.conf. This is based on Todo Item: http://archives.postgresql.org/pgsql-hackers/2011-06/msg02204.php Purpose - This will allow to catch syntax errors in pg_ident at the startup or reload time. Changes are described as follows: a. Make the load_ident() functionality same as load_hba, such that it cleans the previous context, after successful parsing. b. Change the load_ident(), so that parsing can be done during load time and the parsed lines are saved. c. Change the functionality of parse_ident_usermap() so that parsing is not done during authentication. d. If load_ident() fails for parsing, it returns false and error is issued. Looks good to me, committed with some small cleanup. This point I am not sure, as for pg_hba failure it issues FATAL at startup. Currently I have kept error handling for load of pg_ident same as pg_hba I think we should be more lenient with pg_ident.conf, and behave as if the file was empty. That is the old behavior, and it seems sensible. You can still connect using an authentication method that doesn't use pg_ident.conf, but if pg_hba.conf is missing, you cannot log in at all. Thanks! - Heikki -- 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] 64-bit API for large object
Kohei KaiGai kai...@kaigai.gr.jp writes: My preference is nothing are changed both pg_getmsgint64() of the backend and routines under PQfn() of the libpq. Isn't it unavailable to deliver int64- value after the endian translation on the caller side? Right. If we had to change anything on the backend side, it would mean we had a wire protocol change, which is even less acceptable than a libpq ABI change. The patch does not touch pg_getmsgint64() and I don't think we are not going have a wire protocol change. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] 64-bit API for large object
2012/9/21 Tatsuo Ishii is...@postgresql.org: Kohei KaiGai kai...@kaigai.gr.jp writes: My preference is nothing are changed both pg_getmsgint64() of the backend and routines under PQfn() of the libpq. Isn't it unavailable to deliver int64- value after the endian translation on the caller side? Right. If we had to change anything on the backend side, it would mean we had a wire protocol change, which is even less acceptable than a libpq ABI change. The patch does not touch pg_getmsgint64() and I don't think we are not going have a wire protocol change. It's also uncertain what portion does Tom said right for... What I pointed out is this patch adds a special case handling on pqFunctionCall3 of libpq to fetch 64bit-integer from PQArgBlock-u.ptr and adjust endian orders. It is never the topic on backend side. It is not a technical problem, but I feel a bit strange coding style. So, I don't want to against it so much. Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Suggestion for --truncate-tables to pg_restore
On 09/20/2012 12:24:49 PM, Karl O. Pinc wrote: I've had problems using pg_restore --data-only when restoring individual schemas (which contain data which has had bad things done to it). --clean does not work well because of dependent objects in other schemas. Before doing any more work I want to report on the discussions that took place at the code sprint at Postgres Open in Chicago. Because I'm going to add in additional thoughts I've had and to avoid mis-representing anybody's opinion I'll not mention who said what. Feel free to step forward and claim Ingenious Ideas as your own. Likewise I apologize if lack of attribution makes it more difficult to discern (my) uninformed drivel from intelligent insight. First, the problem: Begin with the following structure: CREATE TABLE schemaA.foo (id PRIMARY KEY, data INT); CREATE VIEW schemaB.bar AS SELECT * FROM schemaA.foo; Then, by accident, somebody does: UPDATE schemaA.foo SET data = data + (RANDOM() * 1000)::INT; So, you want to restore the data into schemaA.foo. But schemaA.foo has (bad) data in it that must first be removed. It would seem that using pg_restore --clean -n schemaA -t foo my_pg_dump_backup would solve the problem, it would drop schemaA.foo, recreate it, and then restore the data. But this does not work. schemaA.foo does not drop because it's got a dependent database object, schemaB.bar. Of course there are manual work-arounds. One of these is truncating schemaA.foo and then doing a pg_restore with --data-only. The manual work-arounds become increasingly burdensome as you need to restore more tables. The case that motivated me was an attempt to restore the data in an entire schema, one which contained a significant number of tables. So, the idea here is to be able to do a data-only restore, first truncating the data in the tables being restored to remove the existing corrupted data. The proposal is to add a --truncate-tables option to pg_restore. There were some comments on syntax. I proposed to use -u as a short option. This was thought confusing, given it's use in other Unix command line programs (mysql). Since there's no obvious short option, forget it. Just have a long option. Another choice is to avoid introducing yet another option and instead overload --clean so that when doing a --data-only restore --clean truncates tables and otherwise --clean retains the existing behavior of dropping and re-creating the restored objects. (I tested pg_restore with 9.1 and when --data-only is used --clean is ignored, it does not even produce a warning. This is arguably a bug.) More serious objections were raised regarding semantics. What if, instead, the initial structure looked like: CREATE TABLE schemaA.foo (id PRIMARY KEY, data INT); CREATE TABLE schemaB.bar (id INT CONSTRAINT bar_on_foo REFERENCES foo , moredata INT); With a case like this, in most real-world situations, you'd have to use pg_restore with --disable-triggers if you wanted to use --data-only and --truncate-tables. The possibility of foreign key referential integrity corruption is obvious. Aside: Unless you're restoring databases in their entirety the pg_restore --disable-triggers option makes it easy to introduce foreign key referential integrity corruption. In fact, since pg_restore does not wrap it's operations in one big transaction, it's easy to attempt restoration of a portion of a database, have part of the process succeed and part of it fail (due to either schema or data dependencies), and be left off worse than before you started. The pg_restore docs might benefit from a big fat warning regarding attempts to restore less than an entire database. So, the discussion went, pg_restore is just another application and introducing more options which could lead to corruption of referential integrity is a bad idea. But pg_restore should not be thought of as just another front-end. It should be thought of as a data recovery tool. Recovering some data and being left with referential integrity problems is better than having no data. This is true even if, due to different users owning different schemas and so forth, nobody knows exactly what might be broken. Yes, but we can do better. (The unstated sub-text being that we don't want to introduce an inferior feature which will then need to be supported forever.) How could we do better: Here I will record only the ideas related to restore, although there was some mention of dump as well. There has apparently been some discussion of writing a foreign data wrapper which would operate on a database dump. This might (in ways that are not immediately obvious to me) address this issue. The restore process could, based on what table data needs restoration, look at foreign key dependencies and produce a list of the tables which all must be restored into order to ensure foreign key referential integrity. In the case of restoration into a empty database the foreign key
Re: [HACKERS] DB Schema
On 21.09.2012 17:58, m...@rpzdesign.com wrote: During the execution of ProcessUtility() function in /src/backend/xxx/utility.c, the CreateStmt node type is processed to create a table. Is there a global function in the context of the backend process that will deliver what the current database and schema names? There's a global variable MyDatabaseId for the database the backend is connected to. It doesn't change after login. There's no such thing as a current schema, but I think you'll want to take a look at src/backend/catalog/namespace.c, which handles the search_path. There's a variable activeCreationNamespace there; look at the functions in namespace.c to see how it works. - Heikki -- 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] 64-bit API for large object
Kohei KaiGai kai...@kaigai.gr.jp writes: Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Adding anything inside pqFunctionCall is useless, unless we were to add an int64 variant to PQArgBlock, which isn't a good idea because it will be an ABI break. The functions in fe-lobj.c have to set up the int64 value as if it were pass-by-reference, which means dealing with endianness concerns there. 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] CREATE SCHEMA IF NOT EXISTS
On Sep 21, 2012, at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: I don't believe this has been thought through nearly carefully enough. If CREATE SCHEMA created a schema and nothing more, then the proposed implementation would probably be fine. But per spec, CREATE SCHEMA can specify not only creating the schema but a whole bunch of objects within the schema. As coded, if the schema exists then creation of the specified sub-objects is just skipped, regardless of whether they exist or not. I doubt that this is really sane behavior. Would the principle of least astonishment dictate that the IF NOT EXISTS option apply implicitly to each sub-object as well? (If so, we'd have to extend everything that can appear in OptSchemaEltList; most of those commands don't have IF NOT EXISTS options today.) I had no idea about that functionality. Seems very strange. A possible compromise is to allow the IF NOT EXISTS option only without a schema-element list, which I suspect is the only use-case David had in mind to start with anyway. Yes, true. The existing patch added the check in a pretty randomly chosen spot too, with one bad consequence being that if the schema already exists then it will fall out with the wrong user ID in effect, creating a security bug. But I'm not entirely sure where to put the check instead. Should we put it before or after the permissions checks --- that is, should IF NOT EXISTS require that you would have had permission to create the schema? Or, if the schema does exist, should we just call it good anyway? I'm too lazy to look at how other INE options resolved this question, but it seems like we ought to be consistent. Agreed. But if it already exists, where does it currently die? ISTM that would be the point to check, if possible. Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all this that doesn't exist for any other kind of CREATE command, namely that the object might have been requested to be created under some other user id. For instance, supposing that we were to go forward with trying to create sub-objects, but the ownership of the existing schema is different from what's implied or specified by CREATE SCHEMA, should the sub-objects be (attempted to be) created as owned by that user instead? Perhaps not, but I'm not at all sure. I tend to think that if the schema exists, there should be no attempt to create the sub-objects. Seems the least astonishing to me. Best, David -- 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] CREATE SCHEMA IF NOT EXISTS
Dickson S. Guedes lis...@guedesoft.net writes: I reviewed this v5 of patch: - https://commitfest.postgresql.org/action/patch_view?id=907 The patch is small and implements a new syntax to CREATE SCHEMA that allow the creation of a schema be skipped when IF NOT EXISTS is used. I don't believe this has been thought through nearly carefully enough. If CREATE SCHEMA created a schema and nothing more, then the proposed implementation would probably be fine. But per spec, CREATE SCHEMA can specify not only creating the schema but a whole bunch of objects within the schema. As coded, if the schema exists then creation of the specified sub-objects is just skipped, regardless of whether they exist or not. I doubt that this is really sane behavior. Would the principle of least astonishment dictate that the IF NOT EXISTS option apply implicitly to each sub-object as well? (If so, we'd have to extend everything that can appear in OptSchemaEltList; most of those commands don't have IF NOT EXISTS options today.) This is somewhat connected to our previous arguments about whether CINE ought to try to make any guarantees about whether the existing object is at all like the object that would have been created if the statement executed successfully. I realize that the existing statements that have an INE option have entirely blown off this concern, but I still think that it's a question that ought to be considered, particularly for objects that have separately creatable sub-structure. A possible compromise is to allow the IF NOT EXISTS option only without a schema-element list, which I suspect is the only use-case David had in mind to start with anyway. The existing patch added the check in a pretty randomly chosen spot too, with one bad consequence being that if the schema already exists then it will fall out with the wrong user ID in effect, creating a security bug. But I'm not entirely sure where to put the check instead. Should we put it before or after the permissions checks --- that is, should IF NOT EXISTS require that you would have had permission to create the schema? Or, if the schema does exist, should we just call it good anyway? I'm too lazy to look at how other INE options resolved this question, but it seems like we ought to be consistent. Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all this that doesn't exist for any other kind of CREATE command, namely that the object might have been requested to be created under some other user id. For instance, supposing that we were to go forward with trying to create sub-objects, but the ownership of the existing schema is different from what's implied or specified by CREATE SCHEMA, should the sub-objects be (attempted to be) created as owned by that user instead? Perhaps not, but I'm not at all sure. 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
[HACKERS] External Replication
Heikki: Thanks for the response. I am writing an external replication engine and putting hooks into postgres to send signals via a unix socket to the outside world. All of the logic and implementation will occur OUTSIDE the postgres codebase and will not interfere with any WAL based replication schemes. The usual Trigger level asynch replication does on not suffice since it does not handle new databases, new schemas, new tables, new indexes, alter everything, new functions, etc. So I started putting into utility.c-xxx_ProcessUtility(..., stmt* parsetree,) so that discrete Does anybody have other ideas where to better locate the Hooks for external replication/signaling than utility.c? One drawback is that I have to patch directly into those areas where new relation IDs are created so I can pass them outside of the process to the replication engine. Process Utility does a really bad job of retaining its work as it processes the statements, so I have to patch code where the relation IDS are created. Those new IDS are never retained when leaving ProcessUtility, its work is done. Is there a way to put a Trigger on pg_class, pg_database, pg_namespace instead of patching the statically compiled binary to simulate the triggers? Cheers, marco On 9/21/2012 10:15 AM, Heikki Linnakangas wrote: On 21.09.2012 17:58, m...@rpzdesign.com wrote: During the execution of ProcessUtility() function in /src/backend/xxx/utility.c, the CreateStmt node type is processed to create a table. Is there a global function in the context of the backend process that will deliver what the current database and schema names? There's a global variable MyDatabaseId for the database the backend is connected to. It doesn't change after login. There's no such thing as a current schema, but I think you'll want to take a look at src/backend/catalog/namespace.c, which handles the search_path. There's a variable activeCreationNamespace there; look at the functions in namespace.c to see how it works. - Heikki -- 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] External Replication
On Friday, September 21, 2012 08:12:26 PM m...@rpzdesign.com wrote: Heikki: Thanks for the response. I am writing an external replication engine and putting hooks into postgres to send signals via a unix socket to the outside world. All of the logic and implementation will occur OUTSIDE the postgres codebase and will not interfere with any WAL based replication schemes. The usual Trigger level asynch replication does on not suffice since it does not handle new databases, new schemas, new tables, new indexes, alter everything, new functions, etc. So I started putting into utility.c-xxx_ProcessUtility(..., stmt* parsetree,) so that discrete Does anybody have other ideas where to better locate the Hooks for external replication/signaling than utility.c? Look into the new event triggers started recently. Commits http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3855968f328918b6cd1401dd11d109d471a54d40 and 3a0e4d36ebd7f477822d5bae41ba121a40d22ccc Look into earlier discussions around event/command triggers why putting stuff plainly into ProcessUtility is not all that helpful... Greetings, Andres -- 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] External Replication
Andres: Thanks for the link on the GIT patch code. I did a big major domo search and found some stuff related to command triggers: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php Look into the new event triggers started recently. - Are these command triggers currently in the 9.2.0 code base or is it in a alpha 9.2.1xxx? I searched the 9.2.0 code base and found nothing on CREATE TRIGGER that had anything to do with other than TABLES and VIEWS. I cannot wait for stable code to arrive, I need to add it today. Since the hackers group is already working on this, I will not even try to contribute this work, unless someone wants it. marco On 9/21/2012 1:19 PM, Andres Freund wrote: On Friday, September 21, 2012 08:12:26 PM m...@rpzdesign.com wrote: Heikki: Thanks for the response. I am writing an external replication engine and putting hooks into postgres to send signals via a unix socket to the outside world. All of the logic and implementation will occur OUTSIDE the postgres codebase and will not interfere with any WAL based replication schemes. The usual Trigger level asynch replication does on not suffice since it does not handle new databases, new schemas, new tables, new indexes, alter everything, new functions, etc. So I started putting into utility.c-xxx_ProcessUtility(..., stmt* parsetree,) so that discrete Does anybody have other ideas where to better locate the Hooks for external replication/signaling than utility.c? Look into the new event triggers started recently. Commits http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3855968f328918b6cd1401dd11d109d471a54d40 and 3a0e4d36ebd7f477822d5bae41ba121a40d22ccc Look into earlier discussions around event/command triggers why putting stuff plainly into ProcessUtility is not all that helpful... Greetings, Andres -- 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] External Replication
Hi, On Friday, September 21, 2012 11:06:46 PM m...@rpzdesign.com wrote: Thanks for the link on the GIT patch code. I did a big major domo search and found some stuff related to command triggers: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php Look into the new event triggers started recently. - Are these command triggers currently in the 9.2.0 code base or is it in a alpha 9.2.1xxx? Its not in 9.2 and will only be in 9.3 Andres -- 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] 64-bit API for large object
Tom, Kaigai, Kohei KaiGai kai...@kaigai.gr.jp writes: Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Adding anything inside pqFunctionCall is useless, unless we were to add an int64 variant to PQArgBlock, which isn't a good idea because it will be an ABI break. The functions in fe-lobj.c have to set up the int64 value as if it were pass-by-reference, which means dealing with endianness concerns there. I just want to make sure you guy's point. We do not modify pqFunctionCall. That means PQfn does not accept PQArgBlock.isint != 0 and PQArgBlock.len == 8 case. If a PQfn caller wants to send 64-bit integer, it should set PQArgBlock.isint = 0 and PQArgBlock.len = 8 and set data pass-by-reference. Endianness should be taken care by the PQfn caller. Also we do not modify fe-misc.c because there's no point to add pqPutint64/pqGetint64(they are called from pqFunctionCall in the patch). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] CREATE SCHEMA IF NOT EXISTS
On Sat, Sep 22, 2012 at 3:06 AM, David E. Wheeler da...@justatheory.comwrote: On Sep 21, 2012, at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: I don't believe this has been thought through nearly carefully enough. If CREATE SCHEMA created a schema and nothing more, then the proposed implementation would probably be fine. But per spec, CREATE SCHEMA can specify not only creating the schema but a whole bunch of objects within the schema. As coded, if the schema exists then creation of the specified sub-objects is just skipped, regardless of whether they exist or not. I doubt that this is really sane behavior. Would the principle of least astonishment dictate that the IF NOT EXISTS option apply implicitly to each sub-object as well? (If so, we'd have to extend everything that can appear in OptSchemaEltList; most of those commands don't have IF NOT EXISTS options today.) I had no idea about that functionality. Seems very strange. Isn't it this SQL? CREATE SCHEMA foo CREATE TABLE aa (a int) CREATE TABLE bb (b int); -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] pg_reorg in core?
(2012/09/21 22:32), Michael Paquier wrote: On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo daniele.varra...@gmail.com mailto:daniele.varra...@gmail.com wrote: On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt schmi...@gmail.com mailto:schmi...@gmail.com wrote: I haven't submitted it to PGXN as I prefer the original author to keep the ownership. Thanks, I merged your patches with the dev branch for the time being. It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn. Hi, I'm Sakamoto, reorg mainainer. I'm very happy Josh, Michael and Daniele are interested in reorg. I'm working on the next version of reorg 1.1.8, which will be released in a couple of days. And I come to think that it is a point to reconsider the way to develop/maintain. To be honest, we have little available development resources, so no additional features are added recently. But features and fixes to be done (as Josh sums up. thanks). I think it is a good idea to develop on github. Michael's repo is the root? After the release of 1.1.8, I will freeze CVS repository and create a mirror on github. # Or Michael's repo will do :) I have received some patches from Josh, Daniele. It should be developed in the next major version 1.2. So some of them may not be included in 1.1.8 (caz it's minor versionup), but I feel so appreciated. I think we can discuss further at reorg list. Sakamoto -- 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] pg_reorg in core?
On Sat, Sep 22, 2012 at 9:08 AM, sakamoto dsakam...@lolloo.net wrote: (2012/09/21 22:32), Michael Paquier wrote: On Fri, Sep 21, 2012 at 9:33 PM, Daniele Varrazzo daniele.varra...@gmail.com mailto:daniele.varrazzo@**gmail.comdaniele.varra...@gmail.com wrote: On Fri, Sep 21, 2012 at 5:17 AM, Josh Kupershmidt schmi...@gmail.com mailto:schmi...@gmail.com wrote: I haven't submitted it to PGXN as I prefer the original author to keep the ownership. Thanks, I merged your patches with the dev branch for the time being. It would be great to have some input from the maintainers of pg_reorg in pgfoundry to see if they agree about putting it in pgxn. Hi, I'm Sakamoto, reorg mainainer. I'm very happy Josh, Michael and Daniele are interested in reorg. I'm working on the next version of reorg 1.1.8, which will be released in a couple of days. And I come to think that it is a point to reconsider the way to develop/maintain. To be honest, we have little available development resources, so no additional features are added recently. But features and fixes to be done (as Josh sums up. thanks). I think it is a good idea to develop on github. Michael's repo is the root? After the release of 1.1.8, I will freeze CVS repository and create a mirror on github. # Or Michael's repo will do :) As you wish. You could create a root folder based on a new organization, or on your own account, or use my repo. The result will be the same. I let it at your appreciation I have received some patches from Josh, Daniele. It should be developed in the next major version 1.2. So some of them may not be included in 1.1.8 (caz it's minor versionup), but I feel so appreciated. Great! -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] 64-bit API for large object
Tom, Kaigai, Kohei KaiGai kai...@kaigai.gr.jp writes: Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Adding anything inside pqFunctionCall is useless, unless we were to add an int64 variant to PQArgBlock, which isn't a good idea because it will be an ABI break. The functions in fe-lobj.c have to set up the int64 value as if it were pass-by-reference, which means dealing with endianness concerns there. I just want to make sure you guy's point. We do not modify pqFunctionCall. That means PQfn does not accept PQArgBlock.isint != 0 and PQArgBlock.len == 8 case. If a PQfn caller wants to send 64-bit integer, it should set PQArgBlock.isint = 0 and PQArgBlock.len = 8 and set data pass-by-reference. Endianness should be taken care by the PQfn caller. Also we do not modify fe-misc.c because there's no point to add pqPutint64/pqGetint64(they are called from pqFunctionCall in the patch). Oops. There's no such a function pqGetint64 in the patch. 64-bit int case is taken care inside pqGetint. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] pg_reorg in core?
If the present project is having a tough time doing enhancements, I should think it mighty questionable to try to draw it into core, that presses it towards a group of already very busy developers. On the other hand, if the present development efforts can be made more public, by having them take place in a more public repository, that at least has potential to let others in the community see and participate. There are no guarantees, but privacy is liable to hurt. I wouldn't expect any sudden huge influx of developers, but a steady visible stream of development effort would be mighty useful to a merge into core argument. A *lot* of projects are a lot like this. On the Slony project, we have tried hard to maintain this sort of visibility. Steve Singer, Jan Wieck and I do our individual efforts on git repos visible at GitHub to ensure ongoing efforts aren't invisible inside a corporate repo. It hasn't led to any massive of extra developers, but I am always grateful to see Peter Eisentraut's bug reports.
Re: [HACKERS] pg_reorg in core?
(2012/09/22 10:02), Christopher Browne wrote: If the present project is having a tough time doing enhancements, I should think it mighty questionable to try to draw it into core, that presses it towards a group of already very busy developers. On the other hand, if the present development efforts can be made more public, by having them take place in a more public repository, that at least has potential to let others in the community see and participate. There are no guarantees, but privacy is liable to hurt. I wouldn't expect any sudden huge influx of developers, but a steady visible stream of development effort would be mighty useful to a merge into core argument. A *lot* of projects are a lot like this. On the Slony project, we have tried hard to maintain this sort of visibility. Steve Singer, Jan Wieck and I do our individual efforts on git repos visible at GitHub to ensure ongoing efforts aren't invisible inside a corporate repo. It hasn't led to any massive of extra developers, but I am always grateful to see Peter Eisentraut's bug reports. Agreed. What reorg project needs first is transparency, including issue traking, bugs, listup todo items, clearfied release schedules, quarity assurance and so force. Only after all that done, the discussion to put them to core can be started. Until now, reorg is developed and maintained behind corporate repository. But now that its activity goes slow, what I should do as a maintainer is to try development process more public and finds someone to corporate with:) Sakamoto -- 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: [WIP] Patch : Change pg_ident.conf parsing to be the same as pg_hba.conf
On Friday, September 21, 2012 8:28 PM Heikki Linnakangas wrote: On 02.07.2012 15:08, Amit Kapila wrote: Attached is a Patch to change the parsing of pg_ident.conf to make it similar to pg_hba.conf. This is based on Todo Item: http://archives.postgresql.org/pgsql-hackers/2011-06/msg02204.php Purpose - This will allow to catch syntax errors in pg_ident at the startup or reload time. Looks good to me, committed with some small cleanup. Thank you. With Regards, Amit Kapila. -- 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] [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
On Fri, Sep 21, 2012 at 05:16:46PM +0800, Rural Hunter wrote: I am thinking this query needs to be split apart into a UNION where the second part handles TOAST tables and looks at the schema of the _owner_ of the TOAST table. Needs to be backpatched too. OK, I am at a conference now so will not be able to write-up a patch until perhaps next week. You can drop the information schema in the old database and pg_upgrade should run fine. I will test your failure once I create a patch. OK. I will try. I also found some problems on initdb when re-init my pg9.2 db. 1. initdb doesn't create the pg_log dir so pg can not be started after initdb before I create the dir manually. 2. The case issue of db charset name. I installed pg9.1 and pg9.2 with zh_CN.UTF8. But somehow it seems the actual chaset name is stored with lowercase 'zh_CN.utf8' during the install. In this case, I can run the pg_upgrade without problem since they are both lowercase. But when I re-init pg9.2 with option '-E zh_CN.UTF8', pg_upgrade will fail and report that encoding/charset mis-match: one is uppercase and another is lowercase. If I run initdb with '-E zh_CN.utf8', it will tell me there is no such charset in the system. I found a workaround to run initdb with '--lc-collate=zh_CN.utf8 --lc-ctype=zh_CN.utf8 --lc-messages=zh_CN.utf8 --lc-monetary=zh_CN.utf8 --lc-numeric=zh_CN.utf8 --lc-time=zh_CN.utf8'. But the case problem is really confusing. Yes, it sounds very confusing. I wonder if pg_upgrade should do a case-insentive comprison of encodings? Comments? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [v9.3] Extra Daemons (Re: [HACKERS] elegant and effective way for running jobs inside a database)
On Friday, September 21, 2012 6:50 PM Alvaro Herrera wrote: Excerpts from Amit Kapila's message of vie sep 21 02:26:49 -0300 2012: On Thursday, September 20, 2012 7:13 PM Alvaro Herrera wrote: Well, there is a difficulty here which is that the number of processes connected to databases must be configured during postmaster start (because it determines the size of certain shared memory structs). So you cannot just spawn more tasks if all max_worker_tasks are busy. (This is a problem only for those workers that want to be connected as backends. Those that want libpq connections do not need this and are easier to handle.) If not above then where there is a need of dynamic worker tasks as mentioned by Simon? Well, I think there are many uses for dynamic workers, or short-lived workers (start, do one thing, stop and not be restarted). In my design, a worker is always restarted if it stops; otherwise there is no principled way to know whether it should be running or not (after a crash, should we restart a registered worker? We don't know whether it stopped before the crash.) So it seems to me that at least for this first shot we should consider workers as processes that are going to be always running as long as postmaster is alive. On a crash, if they have a backend connection, they are stopped and then restarted. a. Is there a chance that it would have made shared memory inconsitent after crash like by having lock on some structure and crash before releasing it? If such is case, do we need reinitialize the shared memory as well with worker restart? b. do these worker tasks be able to take any new jobs, or whatever they are started with they will do only those jobs? With Regards, Amit Kapila. With Regards, Amit Kapila. -- 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] CREATE SCHEMA IF NOT EXISTS
2012/9/21 David E. Wheeler da...@justatheory.com
On Sep 21, 2012, at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote:
I don't believe this has been thought through nearly carefully enough.
If CREATE SCHEMA created a schema and nothing more, then the proposed
implementation would probably be fine. But per spec, CREATE SCHEMA
can specify not only creating the schema but a whole bunch of objects
within the schema. As coded, if the schema exists then creation of
the specified sub-objects is just skipped, regardless of whether they
exist or not. I doubt that this is really sane behavior. Would the
principle of least astonishment dictate that the IF NOT EXISTS option
apply implicitly to each sub-object as well? (If so, we'd have to
extend everything that can appear in OptSchemaEltList; most of those
commands don't have IF NOT EXISTS options today.)
I had no idea about that functionality. Seems very strange.
I completely forgot this functionality. The example above is from our docs
[1]:
CREATE SCHEMA hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;
The CREATE SCHEMA statement accepts another CREATE commands (CREATE
{TABLE | VIEW | INDEX | SEQUENCE | TRIGGER}), and the current patch do not
consider this options.
A possible compromise is to allow the IF NOT EXISTS option only without
a schema-element list, which I suspect is the only use-case David had in
mind to start with anyway.
Yes, true.
Ok.
The existing patch added the check in a pretty randomly chosen spot too,
with one bad consequence being that if the schema already exists then it
will fall out with the wrong user ID in effect, creating a security bug.
But I'm not entirely sure where to put the check instead. Should we put
it before or after the permissions checks --- that is, should IF NOT
EXISTS require that you would have had permission to create the schema?
Or, if the schema does exist, should we just call it good anyway? I'm
too lazy to look at how other INE options resolved this question, but it
seems like we ought to be consistent.
Agreed. But if it already exists, where does it currently die? ISTM that
would be the point to check, if possible.
I change the patch (attached) to skip only the schema creation and execute
others statements...
Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all
this that doesn't exist for any other kind of CREATE command, namely
that the object might have been requested to be created under some other
user id. For instance, supposing that we were to go forward with trying
to create sub-objects, but the ownership of the existing schema is
different from what's implied or specified by CREATE SCHEMA, should the
sub-objects be (attempted to be) created as owned by that user instead?
Perhaps not, but I'm not at all sure.
I tend to think that if the schema exists, there should be no attempt to
create the sub-objects. Seems the least astonishing to me.
Why don't create sub-objects? I think the INE clause must affect only
CREATE SCHEMA statement, the others must be executed normally. We can
discuss more about it...
[1] http://www.postgresql.org/docs/9.2/interactive/sql-createschema.html
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
create_schema_if_not_exists_v6.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] PLV8JS
On Fri, Sep 21, 2012 at 3:14 PM, Milton Labanda 1000ton@gmail.com wrote: Hi friends, wich context is the apropiate to install this plugin? I have Ubuntu 11 x64 architecture postgresql 9,2 libv8-3.1 (system package) but not get install it. Some ideas? Well, I'm not sure if pgsql-hackers is quite the right mailing list for this (there's a plv8 mailing list), but because plv8 is probably going to be so instrumental to many profitable uses I can help you address it quickly, and maybe some other denizens of -hackers will have some opinions, especially because this general problem is going to surface more frequently as number of extensions mushroom. Until a distribution/operating system integrator commits to packaging some version of plv8 and/or v8, I recommend employing static linking of plv8 to a self-downloaded libv8. This lets you handle multiple versions of plv8 and libv8 on the same system (but different database installs) and lets you get the latest and greatest v8s at-will. Alternatively, you may be determined to get the plv8 build to work with your OS of choice, even if they do not have packages. I think that's a longer discussion that'd need to happen at a plv8 mailing list. In general, I think if there is to be a guideline for Postgres extensions it should be to enable static linking of dependencies or perhaps a more detailed guideline involving enabling self-contained concurrent installs of dynamically linked extension dependencies. I am currently in the depths of dependency hell involving some of the GIS libraries and PostGIS and really, really wish I could do either of these. There is a relevant discussion here: https://code.google.com/p/plv8js/issues/detail?id=36#makechanges -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
