Re: [GENERAL] Regarding template1 database
On 4/22/2013 8:08 PM, S H wrote: I want to monitor externally, without changing the database. If there are some system queries which can tell me what are the number of database insertion/updation in the template1 ( including system table it will be helpful for me). the system tables include the shared catalogs like pg_roles, which are common with all databases and are modified by ALTER ROLE etc, and the pg_catalog tables like pg_classes, which get modified when you do things like CREATE TABLE. it also includes system tables like pg_stat_activity, which contain views into postgres's innards, and are never directly modified, just queried. so again, your request doesn't make much sense. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug in 8.4 and resolved
On 04/23/2013 12:29 AM, John R Pierce wrote: On 4/22/2013 3:13 PM, Thomas Kellerer wrote: Abhinav Dwivedi wrote on 22.04.2013 07:12: select * from district where statecode in (Select districtcode from state) Please note that the attribute districtcode is not existent in the table state and if this query i.e. Select districtcode from state is executed in isolation then it correctly throws an error message. But when this incorrect query is made inner query of a nested query then the whole query wrongly returns a dataset. I assume the column districtcode is present in the table district. In that case this is not a bug, this is required by the SQL standard. The sub-query references the column from the outer query. And I don't think this has been changed with 9.x (nor will it ever). if that had been written as an explicit join, there would be less ambiguity. select district.* from district join state using(statecode); assuming that's even what you wanted. That's not an equivalent query. Abhinav is doing a semi-join. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] inserting into date field returns error (COPY/BINARY)
I have implemented a C++ procedure for inserting data using libpq and COPY
WITH BINARY command. All field types are working as expected in PG9.2,
except date:
ERROR: incorrect binary data format
CONTEXT: COPY table, line 1, column date_xyz
The passed value for date is a 64-bit integer, calculated by the same way
as the value for timestamp. Should they be different?
Here is simplified fragment of code:
typedef __int64 PGTimeStamp;
...
pField-dataLength = htonl(sizeof(PGTimeStamp));
DBTIMESTAMP ts = { 2012, 10, 5, 0, 0, 0, 0 };
*(PGTimeStamp*)pField-data = ConvertADOToPG(ts);
...
PGTimeStamp ConvertADOToPG(DBTIMESTAMP *ts)
{
double date = date2j(ts-year, ts-month, ts-day) -
POSTGRES_EPOCH_JDATE;
double time = time2t(ts-hour, ts-minute, ts-second, ts-fraction);
double tmp = date * SECS_PER_DAY + time;
PGTimeStamp rez;
int* ptmp = (int*)tmp;
int* prez = (int*)rez;
prez[0] = htonl(ptmp[1]);
prez[1] = htonl(ptmp[0]);
return rez;
}
I'm definitely missing something, please help.
P.S. Documentation mentions about possible compatibility issues. What are
those?
TY!
Re: [GENERAL] using text search
W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze: I forgot to say that the function is position ( txtseach in txtcomplete) :) Bye Alfonso, thenx But if I may: How can I use that function? In a context of my problem? then again. At the edge of desperation, I'm thinking of writing a function, that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause by string operations, and then EXECUTE it. With (currently) four keywords, I'd expect such function to return results within 5 seconds at most. but I'd expect that there should be a way to tell this to postgresql SQL directly. Isn't it? -R
[GENERAL] Streaming replication and pg_dump
Hi, I have a streaming replication slave, which I've setup for backup purposes only. After running pg_dump on it (took around 8 hours) I ended up with 64 .ready files in pg_xlog/archive_status. These files have been there now for more than 24 hours. These are some related configuration settings I modified, in order to be able to run pg_dump for such amount of time: max_standby_archive_delay = 1h max_standby_streaming_delay = 10h I've been reading postgres documentation, but I still have not a clear understanding of what are the implication of these files being there, if they should be removed by postgres, or if something failed and I should redo the replication from scratch. I'd appreciate any comment. Many thanks.
Re: [GENERAL] Memory usage after upgrade to 9.2.4
2013/4/22 Daniel Cristian Cruz danielcrist...@gmail.com query1: EXPLAIN ANALYZE SELECT ced.id_evento, ced.inicio, ced.termino, ced.evento, ced.id_eventos IS NOT NULL AS aula_dividida, ac.titulo, ced.id_tipo_evento, ced.tipo_evento, ac.media_referencia, p.nome, ef.nome AS nomeEspacoFisico, ( SELECT count ( pre2.presente ) 0 FROM turma.presenca pre2 WHERE pre2.id_aula = ac.id_aula AND pre2.id_evento = ac.id_evento AND pre2.id_diario = '64469' ) AS presenca, ced.id_aula FROM recurso.consulta_evento_diario ced LEFT JOIN recurso.evento e USING ( id_evento ) LEFT JOIN recurso.espaco_fisico ef USING ( id_espaco_fisico ) LEFT JOIN turma.aula_calendario ac USING ( id_aula, id_evento ) LEFT JOIN recurso.evento_participante ep USING ( id_evento ) LEFT JOIN senai.pessoa p USING ( id_pessoa ) WHERE id_diario = '64469' AND ced.id_evento NOT IN ( SELECT ec.id_evento_sobreposto FROM recurso.evento_conflito ec WHERE ec.id_evento_sobreposto = ced.id_evento AND ec.ignorado IS NULL ) AND ced.inicio BETWEEN '2013-04-14 00:00:00' AND '2013-04-20 23:59:59.99' ORDER BY inicio; server 9.1: http://explain.depesz.com/s/fmM server 9.2: http://explain.depesz.com/s/wXm After run this one on server 9.2, RES memory reached 6.5GB, VIRT 15GB. Since there is no response, is this memory usage normal? The same query on version 9.1 doesn't use that much memory. I'm concerned about this because there is just only one report like that. Does someone else has the same pattern when using inherited tables? Just for information, my schema uses one table that is inherited by all others tables; it is an audit record: creator, creation time, creator application, updater, update time, updater application, table name and record id. Thanks, -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [GENERAL] Regression test fails v9.2.4
Sorry - replying to my own question It seems that the problem only occurs when configuring the make with these settings : --with-libraries=/lib64 --with-blocksize=2 --with-wal-blocksize=2 Now, we *could* do a two phase compile, one to perform regression tests and another for deployment. Clearly we don't want to do that. Regards, John Manning From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Manning John Sent: 22 April 2013 11:52 To: pgsql-general@postgresql.org Subject: [GENERAL] Regression test fails v9.2.4 Hi all... In my organisation we build PG from source on SLES11.2 using GCC 4.3. Versions 9.1.x of Postgresql and earlier always passed all of the regression tests but 9.2.3 and .4 fail on the following : Union Join Select views Polymorphism With This happens when using gmake check gmake MAX_CONNECTIONS=10 installcheck Mostly the fails are because the returned data order doesn't match the expected results. Another significant example is this output from the 'union' test : *** /data/vdrive/Workspaces/jsm/apx/apx_system_fix_S4/ds_pgres/source/postgresql-9.2.4/src/test/regress/expected/union.out Mon Apr 1 19:20:36 2013 --- /data/vdrive/Workspaces/jsm/apx/apx_system_fix_S4/ds_pgres/source/postgresql-9.2.4/src/test/regress/results/union.out Fri Apr 19 15:21:59 2013 *** *** 490,504 UNION SELECT * FROM t2) t WHERE ab = 'ab'; ! QUERY PLAN ! --- ! HashAggregate !- Append ! - Index Scan using t1_ab_idx on t1 !Index Cond: ((a || b) = 'ab'::text) ! - Index Only Scan using t2_pkey on t2 !Index Cond: (ab = 'ab'::text) ! (6 rows) reset enable_seqscan; reset enable_indexscan; --- 490,506 UNION SELECT * FROM t2) t WHERE ab = 'ab'; !QUERY PLAN ! - ! Unique !- Sort ! Sort Key: ((t1.a || t1.b)) ! - Append !- Index Scan using t1_ab_idx on t1 ! Index Cond: ((a || b) = 'ab'::text) !- Index Only Scan using t2_pkey on t2 ! Index Cond: (ab = 'ab'::text) ! (8 rows) reset enable_seqscan; reset enable_indexscan; Sorry for the rambling question : is this problem common, i.e. the expected results files need to be changed or... is it just me? :) Regards, John Manning Business Application Services Fujitsu Central Park, Northampton Road, Manchester, M40 5BP Unless otherwise stated, this email has been sent from Fujitsu Services Limited, from Fujitsu (FTS) Limited, or from Fujitsu Telecommunications Europe Limited, together Fujitsu. This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free. Fujitsu Services Limited, registered in England No 96056, registered office 22 Baker Street, London W1U 3BW. Fujitsu (FTS) Limited, registered in England No 03808613, registered office 22 Baker Street, London W1U 3BW. PFU Imaging Solutions Europe Limited, registered in England No 1578652, registered office Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE. Fujitsu Telecommunications Europe Limited, registered in England No 2548187, registered office Solihull Parkway, Birmingham Business Park, Birmingham, B37 7YU. Unless otherwise stated, this email has been sent from Fujitsu Services Limited, from Fujitsu (FTS) Limited, or from Fujitsu Telecommunications Europe Limited, together Fujitsu. This email is only for the use of its intended recipient. Its contents are subject to a duty of confidence and may be privileged. Fujitsu does not guarantee that this email has not been intercepted and amended or that it is virus-free. Fujitsu Services Limited, registered in England No 96056, registered office 22 Baker Street, London W1U 3BW. Fujitsu (FTS) Limited, registered in England No 03808613, registered office 22 Baker Street, London W1U 3BW. PFU Imaging Solutions Europe Limited, registered in England No 1578652, registered office Hayes Park Central, Hayes End Road, Hayes, Middlesex, UB4 8FE. Fujitsu Telecommunications Europe Limited, registered in England No 2548187, registered office Solihull Parkway, Birmingham Business Park, Birmingham, B37 7YU.
Re: [GENERAL] using text search
Hi Rafal This function returns the position where the substring is found, so you could do a query with clause position(table1.field in table2.field) The 0 result is not found and maybe, in your case, is faster the use of internal text functions instead of like comparison... hope helps. Bye El 23/04/2013, a las 11:24, Rafał Pietrak ra...@zorro.isa-geek.com escribió: W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze: I forgot to say that the function is position ( txtseach in txtcomplete) :) Bye Alfonso, thenx But if I may: How can I use that function? In a context of my problem? then again. At the edge of desperation, I'm thinking of writing a function, that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause by string operations, and then EXECUTE it. With (currently) four keywords, I'd expect such function to return results within 5 seconds at most. but I'd expect that there should be a way to tell this to postgresql SQL directly. Isn't it? -R Alfonso Afonso (personal) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting into date field returns error (COPY/BINARY)
On Tue, Apr 23, 2013 at 2:54 AM, Konstantin Izmailov pgf...@gmail.com wrote:
I have implemented a C++ procedure for inserting data using libpq and COPY
WITH BINARY command. All field types are working as expected in PG9.2,
except date:
ERROR: incorrect binary data format
CONTEXT: COPY table, line 1, column date_xyz
The passed value for date is a 64-bit integer, calculated by the same way
as the value for timestamp. Should they be different?
Here is simplified fragment of code:
typedef __int64 PGTimeStamp;
...
pField-dataLength = htonl(sizeof(PGTimeStamp));
DBTIMESTAMP ts = { 2012, 10, 5, 0, 0, 0, 0 };
*(PGTimeStamp*)pField-data = ConvertADOToPG(ts);
...
PGTimeStamp ConvertADOToPG(DBTIMESTAMP *ts)
{
double date = date2j(ts-year, ts-month, ts-day) -
POSTGRES_EPOCH_JDATE;
double time = time2t(ts-hour, ts-minute, ts-second, ts-fraction);
double tmp = date * SECS_PER_DAY + time;
PGTimeStamp rez;
int* ptmp = (int*)tmp;
int* prez = (int*)rez;
prez[0] = htonl(ptmp[1]);
prez[1] = htonl(ptmp[0]);
return rez;
}
I'm definitely missing something, please help.
P.S. Documentation mentions about possible compatibility issues. What are
those?
TY!
date is 4 byte integer. look at libpqtypes datetime.c here:
http://libpqtypes.esilo.com/browse_source.html?file=datetime.c
/* PGdate members required isbc, year, mon, mday */
int
pqt_put_date(PGtypeArgs *args)
{
int dval;
PGdate *date = va_arg(args-ap, PGdate *);
PUTNULLCHK(args, date);
CHECKDATEVALS(args, date);
dval = date2j(BC2YEAR(date-isbc, date-year), date-mon+1, date-mday)
- POSTGRES_EPOCH_JDATE;
pqt_buf_putint4(args-put.out, dval);
return 4;
}
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] using text search
Ha! Got it!
for enybody whois interested:
with tst(regexp) as (SELECT '(' || array_to_string(array_agg(phrase),
'|') || ')' from KEYWORDS) select o.* from ORDERS o, tst t where o.info
~ t.regexp;
execution time: 6400ms. (keywords=4, orders=1mln)
BTW: does anybody know if there is an index, that could improve the
performence of the above regexp?
-R
W dniu 04/23/2013 02:30 PM, Alfonso Afonso pisze:
Hi Rafal
This function returns the position where the substring is found, so you could
do a query with clause position(table1.field in table2.field)
The 0 result is not found and maybe, in your case, is faster the use of
internal text functions instead of like comparison... hope helps.
Bye
El 23/04/2013, a las 11:24, Rafał Pietrak ra...@zorro.isa-geek.com escribió:
W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze:
I forgot to say that the function is position ( txtseach in txtcomplete) :)
Bye
Alfonso, thenx
But if I may: How can I use that function? In a context of my problem?
then again. At the edge of desperation, I'm thinking of writing a function,
that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause
by string operations, and then EXECUTE it. With (currently) four keywords, I'd
expect such function to return results within 5 seconds at most.
but I'd expect that there should be a way to tell this to postgresql SQL
directly. Isn't it?
-R
Alfonso Afonso
(personal)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory usage after upgrade to 9.2.4
On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote: 2013/4/22 Daniel Cristian Cruz danielcrist...@gmail.com mailto:danielcrist...@gmail.com query1: EXPLAIN ANALYZE SELECT ced.id_evento, ced.inicio, ced.termino, ced.evento, ced.id_eventos IS NOT NULL AS aula_dividida, ac.titulo, ced.id_tipo_evento, ced.tipo_evento, ac.media_referencia, p.nome, ef.nome AS nomeEspacoFisico, ( SELECT count ( pre2.presente ) 0 FROM turma.presenca pre2 WHERE pre2.id_aula = ac.id_aula AND pre2.id_evento = ac.id_evento AND pre2.id_diario = '64469' ) AS presenca, ced.id_aula FROM recurso.consulta_evento_diario ced LEFT JOIN recurso.evento e USING ( id_evento ) LEFT JOIN recurso.espaco_fisico ef USING ( id_espaco_fisico ) LEFT JOIN turma.aula_calendario ac USING ( id_aula, id_evento ) LEFT JOIN recurso.evento_participante ep USING ( id_evento ) LEFT JOIN senai.pessoa p USING ( id_pessoa ) WHERE id_diario = '64469' AND ced.id_evento NOT IN ( SELECT ec.id_evento_sobreposto FROM recurso.evento_conflito ec WHERE ec.id_evento_sobreposto = ced.id_evento AND ec.ignorado IS NULL ) AND ced.inicio BETWEEN '2013-04-14 00:00:00' AND '2013-04-20 23:59:59.99' ORDER BY inicio; server 9.1: http://explain.depesz.com/s/fmM server 9.2: http://explain.depesz.com/s/wXm After run this one on server 9.2, RES memory reached 6.5GB, VIRT 15GB. Since there is no response, is this memory usage normal? The same query on version 9.1 doesn't use that much memory. Not sure how it applies but I noticed that a GroupAggregate in 9.1 that took 1.22 secs became a a HashAggregate in the 9.2 query and took 12.54 secs. I'm concerned about this because there is just only one report like that. Does someone else has the same pattern when using inherited tables? Also noticed that in your 9.2 production conf: (no constraint_exclusion set) Does this mean the default of 'partition' was left as is or that the setting was set to 'off'? Just for information, my schema uses one table that is inherited by all others tables; it is an audit record: creator, creation time, creator application, updater, update time, updater application, table name and record id. Thanks, -- Daniel Cristian Cruz クルズ クリスチアン ダニエル -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding template1 database
On 04/22/2013 08:08 PM, S H wrote: - I want to know the tables of tempate1 There are none, unless you choose to add some. I got it there are no tables. What about the system tables of template1 database ? Are they updated or not? - If i want to monitor the transactions done in the template1 database ( insertion/updation ) , is there any way to track the same. I want to monitor externally, without changing the database. If there are some system queries which can tell me what are the number of database insertion/updation in the template1 ( including system table it will be helpful for me). As mentioned earlier the template databases are not really supposed to be used as active databases, but as templates for creating new databases. Also, as John mentioned system tables/views are often global and do not apply to a particular database. It would be helpful to explain what it is you want to accomplish. For example, this is for security purposes and I want to know if someone is using template1. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory usage after upgrade to 9.2.4
2013/4/23 Adrian Klaver adrian.kla...@gmail.com On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote: 2013/4/22 Daniel Cristian Cruz danielcrist...@gmail.com mailto:danielcristian@gmail.**com danielcrist...@gmail.com query1: EXPLAIN ANALYZE SELECT ced.id_evento, ced.inicio, ced.termino, ced.evento, ced.id_eventos IS NOT NULL AS aula_dividida, ac.titulo, ced.id_tipo_evento, ced.tipo_evento, ac.media_referencia, p.nome, ef.nome AS nomeEspacoFisico, ( SELECT count ( pre2.presente ) 0 FROM turma.presenca pre2 WHERE pre2.id_aula = ac.id_aula AND pre2.id_evento = ac.id_evento AND pre2.id_diario = '64469' ) AS presenca, ced.id_aula FROM recurso.consulta_evento_diario ced LEFT JOIN recurso.evento e USING ( id_evento ) LEFT JOIN recurso.espaco_fisico ef USING ( id_espaco_fisico ) LEFT JOIN turma.aula_calendario ac USING ( id_aula, id_evento ) LEFT JOIN recurso.evento_participante ep USING ( id_evento ) LEFT JOIN senai.pessoa p USING ( id_pessoa ) WHERE id_diario = '64469' AND ced.id_evento NOT IN ( SELECT ec.id_evento_sobreposto FROM recurso.evento_conflito ec WHERE ec.id_evento_sobreposto = ced.id_evento AND ec.ignorado IS NULL ) AND ced.inicio BETWEEN '2013-04-14 00:00:00' AND '2013-04-20 23:59:59.99' ORDER BY inicio; server 9.1: http://explain.depesz.com/s/**fmM http://explain.depesz.com/s/fmM server 9.2: http://explain.depesz.com/s/**wXm http://explain.depesz.com/s/wXm After run this one on server 9.2, RES memory reached 6.5GB, VIRT 15GB. Since there is no response, is this memory usage normal? The same query on version 9.1 doesn't use that much memory. Not sure how it applies but I noticed that a GroupAggregate in 9.1 that took 1.22 secs became a a HashAggregate in the 9.2 query and took 12.54 secs. I used to read a explain and find something, but this one is huge. Unfortunately I'm still working on data migration from the 9.2 to 9.1 and didn't get time to read it in detail... I'm concerned about this because there is just only one report like that. Does someone else has the same pattern when using inherited tables? Also noticed that in your 9.2 production conf: (no constraint_exclusion set) Does this mean the default of 'partition' was left as is or that the setting was set to 'off'? No, default: senai=# show constraint_exclusion ; constraint_exclusion -- partition (1 row) -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [GENERAL] Regarding template1 database
I am working on some legacy product. In which template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time.I want to know if there is any major update/insert in template1 at some time, so i wanted to monitor the number of insertions and deletions done in template1. As mentioned earlier the template databases are not really supposed to be used as active databases, but as templates for creating new databases. Also, as John mentioned system tables/views are often global and do not apply to a particular database. It would be helpful to explain what it is you want to accomplish. For example, this is for security purposes and I want to know if someone is using template1. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] make uninstall deletes all manpages?
I just tried make uninstall. I did: sudo make uninstall which goes: make -C doc uninstall make -C src uninstall make -C sgml uninstall rm -f '/opt/local/share/doc//postgresql/html/'* '/opt/local/share/man'/man1/* '/opt/local/share/man'/man3/* '/opt/local/share/man'/man7/* /bin/sh: /bin/rm: Argument list too long So it tries to delete all my manpages. (/opt/local is a macports install). Yes, I realize installing into /opt/local was not such a hot idea to begin with. but I knew I had make uninstall, so I could easily back off, right? Regards Marcin
[GENERAL] Log messages regarding automatic vacuum and exclusive locks
Good morning. I'm seeing several of the following log messages each morning (for example, there were five this morning, spaced approximately one minute apart, with the closest interval between messages being 44 seconds). They're occurring during a daily cycle of deleting all rows from a table and then repopulating it from another database; the first message came approximately seven minutes after the process started and the last one came about a minute before the process ended. There are approximately one million rows in the database after repopulation. LOG: automatic vacuum of table x.y.z: could not (re)acquire exclusive lock for truncate scan I'm using default settings for autovacuum, with server version 9.2.4. Some other smaller tables produce the same messages during the same process, but apparently less frequently. Any thoughts on the cause? Chapters 18 and 23 of the documentation provided some hints, but nothing conclusive. Thanks in advance, Dominic Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regression test fails v9.2.4
Manning John john.mann...@uk.fujitsu.com wrote: [regression tests have different plans or row orderings] It seems that the problem only occurs when configuring the make with these settings : --with-libraries=/lib64 --with-blocksize=2 --with-wal-blocksize=2 is this problem common, i.e. the expected results files need to be changed ... ? I don't find it too surprising that a different page size could cause different plans to be chosen or different row orderings. After verifying that the differences are benign, you could add alternative expected files for your build environment. I can't help being a little curious why you are overriding these defaults. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_stop_backup running for 2h10m?
Hi, I used omnipitr to launch a base backup, but I fumbled a couple of things, so I Ctrl+C'd *once* the console where I had omnipitr-backup-master running. omnipitr-backup-master correctly launched pg_stop_backup, but pg_stop_backup has been active for 2h10 minutes, as reported by pg_stat_activity. According to the docs, pg_stop_backup wants to archive all xlogs before returning, but xlogs aren't being added to the backup directory. What could be preventing pg_stop_backup from returning? Thanks! François Beausoleil smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] pg_stop_backup running for 2h10m?
Le 2013-04-23 à 15:08, François Beausoleil a écrit : I used omnipitr to launch a base backup, but I fumbled a couple of things, so I Ctrl+C'd *once* the console where I had omnipitr-backup-master running. omnipitr-backup-master correctly launched pg_stop_backup, but pg_stop_backup has been active for 2h10 minutes, as reported by pg_stat_activity. According to the docs, pg_stop_backup wants to archive all xlogs before returning, but xlogs aren't being added to the backup directory. What could be preventing pg_stop_backup from returning? For reference, pg_stop_backup() doesn't seem to hold much locks: select * from pg_locks where pid = 14301; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted +--+--+--+---++---+-+---+--++---+---+- virtualxid | | | | | 32/6558| | | | | 32/6558| 14301 | ExclusiveLock | t Bye, François smime.p7s Description: S/MIME cryptographic signature
[GENERAL] run COPY as user other than postgres
I would like to run the COPY command as a user other than postgres. I find it a bit of a pain (or at least requiring an extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simple example where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if I could run the COPY TO as that user. Any ideas? COPY ( SELECT * FROM some_table WHERE 2012 = EXTRACT (YEAR FROM some_column) --AND value IS NOT NULL ) TO '/some/path/to/file/file.csv' WITH CSV HEADER; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] run COPY as user other than postgres
On Tue, Apr 04/23/13, 2013 at 03:11:21PM -0500, Kirk Wythers wrote: I would like to run the COPY command as a user other than postgres. I find it a bit of a pain (or at least requiring an extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simple example where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if I could run the COPY TO as that user. Any ideas? You should use \copy if you're using psql. That will run on the client side, as the user running psql. -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Negative replication lag?
Ah, that makes sense. I think I'll add some logic to the script that has it get new data points if it comes up with a negative value. Thanks for the insight. QH On Mon, Apr 22, 2013 at 5:11 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-04-22 16:36:38 -0600, Quentin Hartman wrote: I'm using this script to check my replication lag on my streaming replication pairs with Nagios: https://gist.github.com/jacobian/743942 It generally works fine, but will occasionally return a negative lag value (-37kb for example) which of course causes it to throw an alarm, but is total nonsense. I've been working on the assumption that it is some sort of bug in the script, but in taking a quick look at it nothing jumps out at me. Is there something in Postgres itself that could cause this to happen once in awhile? Is it something to be concerned about? Is there a better way to monitor this state? Well, between the time pg_current_xlog_location() is run on the primary and pg_last_xlog_replay_location() on the standby some time passes, so its not all that unlikely that wal has been generated, streamed *and* applied in that time. Given the short timeframe it only happens every now and then. Did you check the pg_stat_replication view on the primary? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] pg_stop_backup running for 2h10m?
François Beausoleil franc...@teksol.info wrote: xlogs aren't being added to the backup directory. Any clue in the server log why that is? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_stop_backup running for 2h10m?
Le 2013-04-23 à 16:25, Kevin Grittner a écrit :
François Beausoleil franc...@teksol.info wrote:
xlogs aren't being added to the backup directory.
Any clue in the server log why that is?
I checked but didn't mention it. I ended up calling pg_cancel_backend() on the
process, after 3h. strace on the process revealed pg_stop_backup was doing
stat('pg_xlog/archive_status/000###.ready'), but the file never appeared.
As I said, I had written a bad omnipitr command, and was trying to backup to a
non-existent host, which would have failed.
I'm recovered now and the backup's going.
Bye,
François
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] Log messages regarding automatic vacuum and exclusive locks
jon...@xmission.com jon...@xmission.com wrote: I'm seeing several of the following log messages each morning (for example, there were five this morning, spaced approximately one minute apart, with the closest interval between messages being 44 seconds). They're occurring during a daily cycle of deleting all rows from a table and then repopulating it from another database; the first message came approximately seven minutes after the process started and the last one came about a minute before the process ended. There are approximately one million rows in the database after repopulation. LOG: automatic vacuum of table x.y.z: could not (re)acquire exclusive lock for truncate scan I'm using default settings for autovacuum, with server version 9.2.4. Some other smaller tables produce the same messages during the same process, but apparently less frequently. Any thoughts on the cause? Chapters 18 and 23 of the documentation provided some hints, but nothing conclusive. As long as these messages stop before the statistics for the related table have a chance to get out line with reality, these messages can be safely ignored. They are happening because autovacuum noticed a large amount of free space at the end of the table, and was attempting to remove that space from the table and give it back to the filesystem, but noticed conflicting access to the table. It steps aside to avoid holding up the other process, and is just making a note of that. If the table is being repopulated, it is probably just as well that autovacuum does not truncate the table, since that would just result in reallocation as rows are added. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding template1 database
On 4/24/13, S H msq...@live.com wrote: I am working on some legacy product. In which template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time.I want to know if there is any major update/insert in template1 at some time, so i wanted to monitor the number of insertions and deletions done in template1. Sounds like you have used template1 as your live database. OK, so from your point of view, template1 IS your database. In that case, perhaps google for postgresql transaction monitoring or similar. Others might be able to give you better advice/links. Good luck, Zenaan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding template1 database
On 04/23/2013 07:26 AM, S H wrote: I am working on some legacy product. In which template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time. I want to know if there is any major update/insert in template1 at some time, so i wanted to monitor the number of insertions and deletions done in template1. What version of Postgres are you using? If it is new enough autovacuum was created to deal with your situation. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding template1 database
On 4/23/2013 7:26 AM, S H wrote: ... template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time what do you get from... $ sudo -u postgres psql template1 psql (9.2.4) Type help for help. template1=# \d No relations found. thats normal above. do you have tables and stuff in this template1 database? and to see about the rest... template1=# \l List of databases Name |Owner| Encoding | Collate |Ctype | Access privileges +-+---+-+-+ blogs | blogs | UTF8 | en_US.UTF-8 | en_US.UTF-8 | junk | postgres| UTF8 | en_US.UTF-8 | en_US.UTF-8 | pierce | pierce | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pierce=CTc/pierce + | | | | | =Tc/pierce template0 | postgres| UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres| UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=C*T*/postgres+ | | | | | =c/postgres (5 rows) template1=# -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Log messages regarding automatic vacuum and exclusive locks
On Tue, Apr 23, 2013 at 8:50 AM, jon...@xmission.com wrote: Good morning. I'm seeing several of the following log messages each morning (for example, there were five this morning, spaced approximately one minute apart, with the closest interval between messages being 44 seconds). They're occurring during a daily cycle of deleting all rows from a table and then repopulating it from another database; the first message came If all the rows are deleted from the table every time to repopulate it later, I would suggest using TRUNCATE table_name; instead DELETE FROM table_name. TRUNCATE does not left any free space in the table so autovacuum will have nothing to do with it. LOG: automatic vacuum of table x.y.z: could not (re)acquire exclusive lock for truncate scan Hence you will not get this messages and BTW it will be faster. I'm using default settings for autovacuum, with server version 9.2.4. Some other smaller tables produce the same messages during the same process, but apparently less frequently. Any thoughts on the cause? Chapters 18 and 23 of the documentation provided some hints, but nothing conclusive. Thanks in advance, Dominic Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] run COPY as user other than postgres
On Tue, Apr 23, 2013 at 1:11 PM, Kirk Wythers wythe...@umn.edu wrote: I would like to run the COPY command as a user other than postgres. I find it a bit of a pain (or at least requiring an extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simple example where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if I could run the COPY TO as that user. Any ideas? sudo chown anotheruser:postgres /some/path/to/file/file.csv sudo chmod 664 /some/path/to/file/file.csv This will set the file's group to postgres and allow it's members to write to the file. COPY ( SELECT * FROM some_table WHERE 2012 = EXTRACT (YEAR FROM some_column) --AND value IS NOT NULL ) TO '/some/path/to/file/file.csv' WITH CSV HEADER; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inserting into date field returns error (COPY/BINARY)
Merlin, it worked like a charm! Thank you tons! Konstantin
[GENERAL] FATAL: shmat(id=3342337) failed: Cannot allocate memory
Hi all We are running postgres 9.1 on CentOS 5. We can’t start up postgres . It complains about memory allocation . Please help FATAL: shmat(id=3342337) failed: Cannot allocate memory # - Memory - shared_buffers = 310251 # (change requires restart) #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. work_mem = 32MB # min 64kB maintenance_work_mem = 512MB# min 1MB #max_stack_depth = 2MB # min 100kB /etc/sysctl.conf # Controls the default maxmimum size of a mesage queue kernel.msgmax = 65536 # Controls the maximum shared segment size, in bytes kernel.shmmax = 4294967295 # Controls the maximum number of shared memory segments, in pages kernel.shmall = 26843545 -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x7a014a02 32768 zabbix666971500 6 -- Semaphore Arrays keysemid owner perms nsems 0x7a014a02 0 zabbix6663 -- Message Queues keymsqid owner perms used-bytes messages Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] FATAL: shmat(id=3342337) failed: Cannot allocate memory
On 4/23/2013 10:53 PM, Khangelani Gama wrote: We are running postgres 9.1 on CentOS 5. We can’t start up postgres . It complains about memory allocation . Please help FATAL: shmat(id=3342337) failed: Cannot allocate memory is that the entire error message? it usually says something about setting kernel.shmmax and/or shmall... for most flavors of linux, you set these in /etc/sysctl.conf (and then update the live system with sysctl -p). -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
