[GENERAL] Installing PostgreSQL 10 on Mac OSX Undefined Symbol _heap_modify_tuple_by_cols
G'day, we are quite excited about the parallelisation enhancements, and keen to try, but trying to build (using the same configure as we have used for 9.6) is giving some warnings and errors. The detail is below, but the oddity I'm really wondering about is the reference in the command to /usr/local/pgsql965/... this was my current 9.6 install (which went smoothly) - but why is it being referenced in the make for 10...? Is this looking for an existing environment variable (which seems unlikely for a build process) or is something else unusual? I am still on Sierra (Darwin orion.local 16.7.0 Darwin Kernel Version 16.7.0: Thu Jun 15 17:36:27 PDT 2017; root:xnu-3789.70.16~2/RELEASE_X86_64 x86_64) and using this configure: ./configure --prefix=/usr/local/pgsql-10 --with-extra-version=BM --with-python --with-openssl --with-bonjour --with-uuid=e2fs --with-libxml --with-libxslt PYTHON=/Library/Frameworks/Python.framework/Versions/3.6/bin/python3 and the build failure ends with: /Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../../../contrib/spi gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -arch x86_64 -DREFINT_VERBOSE -I. -I./ -I/usr/local/pgsql965/include/server -I/usr/local/pgsql965/include/internal -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2 -I/usr/local/include -c -o autoinc.o autoinc.c autoinc.c:116:14: warning: implicit declaration of function 'heap_modify_tuple_by_cols' is invalid in C99 [-Wimplicit-function-declaration] rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, ^ autoinc.c:116:12: warning: incompatible integer to pointer conversion assigning to 'HeapTuple' (aka 'struct HeapTupleData *') from 'int' [-Wint-conversion] rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, ^ 2 warnings generated. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -arch x86_64 -L/usr/local/pgsql965/lib -L/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/lib -L/usr/local/lib -Wl,-dead_strip_dylibs -arch x86_64 -L/usr/local/pgsql965/lib/pgxs/src/makefiles/../../src/port -lpgport -bundle -bundle_loader /usr/local/pgsql965/bin/postgres -o autoinc.so autoinc.o ld: warning: directory not found for option '-L/usr/local/pgsql965/lib/pgxs/src/makefiles/../../src/port' Undefined symbols for architecture x86_64: "_heap_modify_tuple_by_cols", referenced from: _autoinc in autoinc.o ld: symbol(s) not found for architecture x86_64 clang: error: linker command failed with exit code 1 (use -v to see invocation) make[3]: *** [autoinc.so] Error 1 make[2]: *** [submake-contrib-spi] Error 2 make[1]: *** [all-test/regress-recurse] Error 2 make: *** [all-src-recurse] Error 2 cheers Ben -- Ben Madin m : +61 448 887 220 w : +61 8 7200 7220 e : b...@ausvet.com.au 5 Shuffrey Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments.
Re: [GENERAL] Postgres 9.6 fails to start on VMWare
On Sun, 2017-10-22 at 15:13 +0100, Martin Moore wrote: > 2017-10-22 14:08:28 UTC [2479-1] LOG: 0: database system > shutdown was interrupted; last known up at 2017-10-22 14:07:20 UTC There is something missing here. Last shutdown at 2017-10-22 14:07:20 UTC on which server? Then attempting to start it at 2017-10-22 14:08:28 UTC? One minute and eight seconds later. It might also help if you explained exactly how you moved the database from Google Compute to this VM machine. Cheers, robert -- 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] Postgres 9.6 fails to start on VMWare
On Sun, Oct 22, 2017 at 11:13 PM, Martin Moore wrote: > I’ve migrated a running Debian Jessie system from a Google Compute instance > to a VMWare ESXi 6.5 system. How did you actually do this migration? It is really easy to finish with a corrupted instance if not doing things correctly in this world (I am referring to quiesced snapshot & co). -- Michael -- 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] Backup strategy using 'wal_keep_segments'
On Mon, Oct 23, 2017 at 5:57 AM, Rhhh Lin wrote: > Is this approach feasible? Assuming obviously, we have sufficient disk space > to facilitate 1000 WAL files etc. You expose yourself to race conditions with such methods if a checkpoint has the bad idea to recycle past segments that your logic is copying. So I would advise to not do that. Instead of using the archive command, you should also consider using pg_receivexlog combined with a replication slot. This brings way more control with the error handling. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backup strategy using 'wal_keep_segments'
Hi, Version 9.4... Per the PG docs, to facilitate continuous WAL archiving and PITR recovery... "To enable WAL archiving, set the wal_level configuration parameter to archive (or hot_standby), archive_mode to on, and specify the shell command to use in the archive_command configuration parameter." This instruction is fine and I have a solid understanding of the implementation of these parameters, what they do and the why behind them, i.e. in order to maintain a continuous chain of database changes in the WAL stream. My question however is this... A colleague recently suggested that instead of implementing an 'archive_command' to push archivable WALs to a secondary location (for further backup to tape for example), we could instead persist the WAL files in their current location by setting the "wal_keep_segments" parameter to an extreme value e.g. 1000 and have the 'archive_command' do nothing. So, something like... wal_keep_segments=1000 archive_command='cd .' And then periodically copy the archived WAL_files from the pg_xlog directory out to tape, removing as we go? Is this approach feasible? Assuming obviously, we have sufficient disk space to facilitate 1000 WAL files etc. But from a point-in-time recovery, and backup perspective - are we missing anything if we were to adopt this non-standard approach? Regards
Re: [GENERAL] parray_gin and \d errors in PG10
I wrote: > Or maybe what we should do is to avoid @> in favor of using > ('d' = any(stxkind)) Pushed that way. regards, tom lane -- 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] tgrm index for word_similarity
On Sat, Oct 21, 2017 at 10:01:56PM -0700, Igal @ Lucee.org wrote: > > 1) I thought that the whole idea behind indexes on expressions is that the > index would be used in a WHERE clause? See > https://www.postgresql.org/docs/10/static/indexes-expressional.html - Am I > missing something? > I think the idea is a little bit different. It is about computing index entries only once, during index creation. During scan PostgreSQL doesn't compute such entries every time. I am not very good at PostgreSQL's planner. But I know that PostgreSQL uses index scan for pg_trgm only with %, <%, ~~, ~~*, ~, ~* operators. pg_trgm's operator classes (which should be implemented for index scan) are designed in this way. > 2) A query with `WHERE input <% name` utilizes the index, but a query > without a WHERE clause at all does not? Because sequential scan is cheaper here than index scan. > > 3) What happens if I do not create an index at all? Does the query that I > run in 30 - 40ms, the one that does not utilize an index, creates all of the > tri-grams on the fly each time that it runs? Would it be possible for me to > create a TABLE or a VIEW with the tri-grams so that there is no need to > create them each time the query runs? > As far as I know you can't do it nowadays. You can't create an trigram column, as you can do it for FTS, you can create an tsvector column. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres 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] How to get login user name and host name in pgaudit
On Sun, Oct 22, 2017 at 05:32:56AM -0700, rakeshkumar464 wrote: > I installed latest pgaudit (1.2) with pg10. I am testing it and I see that > it does not log the login user name and host name. > > For example, if user mary is running select * from sensitive_table, I want > Mary and the machine from where she ran in the log. > > It seems to log the ids which needs to be joined with pg_ views to convert > it into login user name and host name. > > any pointers on how to get it done. > > thanks. > According to the README [1] you need to set the log_line_prefix GUC variable [2]. It's default value is '%m [%p]'. For example: =# alter system set log_line_prefix to '%m [%p] %u %h'; =# select pg_reload_conf(); This GUC variable will change all log lines of PostgreSQL, not only pgaudit's. 1 - https://github.com/pgaudit/pgaudit#format 2 - https://www.postgresql.org/docs/10/static/runtime-config-logging.html#guc-log-line-prefix -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres 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] parray_gin and \d errors in PG10
Justin Pryzby writes: > On Sun, Oct 22, 2017 at 02:36:12PM -0400, Tom Lane wrote: >> ... Possibly we could use >> (stxkind @> '{d}'::pg_catalog."char"[]) >> That works for me without parray_gin installed, but I wonder whether >> it fails due to ambiguity if you do have parray_gin installed. > [ yup ] Bleah. One option is to use OPERATOR(pg_catalog.@>), which aside from being really ugly, isn't a complete fix because it still wouldn't work if someone had decided to install parray_gin into the pg_catalog schema. Or maybe what we should do is to avoid @> in favor of using ('d' = any(stxkind)) That's a bit less nice because it doesn't generalize as cheaply to looking for multiple stxkind values, but since this query has no current need for that, maybe it's fine. Anyway, the fact that this is such a mess points up why trying to alias polymorphic operators isn't such a hot idea. parray_gin really ought to get rid of that operator. regards, tom lane -- 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] parray_gin and \d errors in PG10
On Sun, Oct 22, 2017 at 02:36:12PM -0400, Tom Lane wrote: > Justin Pryzby writes: > > After installing parray_gin extension and pg_upgrading another instance, > > \d is failing like so: > > > [pryzbyj@database ~]$ psql ts -c '\d pg_class' > > ERROR: operator is not unique: "char"[] @> unknown > > LINE 6: (stxkind @> '{d}') AS ndist_enabled, > match the anyarray operator. Possibly we could use > > (stxkind @> '{d}'::pg_catalog."char"[]) > > That works for me without parray_gin installed, but I wonder whether > it fails due to ambiguity if you do have parray_gin installed. In > principle this'd still match the text[] @> text[] operator, and I'm > not sure whether we have an ambiguity resolution rule that would > prefer one over the other. ts=# SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns, (stxkind @> '{d}'::pg_catalog."char"[]) AS ndist_enabled, (stxkind @> '{d}'::pg_catalog."char"[]) AS deps_enabled FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259' ORDER BY 1; ERROR: operator is not unique: "char"[] @> "char"[] LINE 6: (stxkind @> '{d}'::pg_catalog."char"[]) AS ndist_enabled, ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Justin -- 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] parray_gin and \d errors in PG10
Justin Pryzby writes: > After installing parray_gin extension and pg_upgrading another instance, > \d is failing like so: > [pryzbyj@database ~]$ psql ts -c '\d pg_class' > ERROR: operator is not unique: "char"[] @> unknown > LINE 6: (stxkind @> '{d}') AS ndist_enabled, Ugh. > Thankfully this is still working: > ts=# \do @> > ... > pg_catalog | @> | anyarray | anyarray | boolean | contains > ... > public | @> | text[]| text[] | boolean | text > array contains compared by strict I'm inclined to think it wasn't very bright of parray_gin to have installed an operator that's confusable with the builtin anyarray @> anyarray operator. Still, we might as well try to work around that. > This query works fine when adding cast to text[]: No, that will fail entirely if you don't have parray_gin installed, because stxkind is of type "char"[], and "char"[] @> text[] will not match the anyarray operator. Possibly we could use (stxkind @> '{d}'::pg_catalog."char"[]) That works for me without parray_gin installed, but I wonder whether it fails due to ambiguity if you do have parray_gin installed. In principle this'd still match the text[] @> text[] operator, and I'm not sure whether we have an ambiguity resolution rule that would prefer one over the other. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] parray_gin and \d errors in PG10
After installing parray_gin extension and pg_upgrading another instance, \d is failing like so: [pryzbyj@database ~]$ psql ts -c '\d pg_class' ERROR: operator is not unique: "char"[] @> unknown LINE 6: (stxkind @> '{d}') AS ndist_enabled, ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. [pryzbyj@database ~]$ psql ts -c '\d pg_class' -E [...] * QUERY ** SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns, (stxkind @> '{d}') AS ndist_enabled, (stxkind @> '{f}') AS deps_enabled FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259' ORDER BY 1; ** ERROR: operator is not unique: "char"[] @> unknown LINE 6: (stxkind @> '{d}') AS ndist_enabled, ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Thankfully this is still working: ts=# \do @> List of operators Schema | Name | Left arg type | Right arg type | Result type | Description +--+---++-+ pg_catalog | @> | aclitem[] | aclitem| boolean | contains pg_catalog | @> | anyarray | anyarray | boolean | contains pg_catalog | @> | anyrange | anyelement | boolean | contains pg_catalog | @> | anyrange | anyrange | boolean | contains pg_catalog | @> | box | box| boolean | contains pg_catalog | @> | box | point | boolean | contains pg_catalog | @> | circle| circle | boolean | contains pg_catalog | @> | circle| point | boolean | contains pg_catalog | @> | jsonb | jsonb | boolean | contains pg_catalog | @> | path | point | boolean | contains pg_catalog | @> | polygon | point | boolean | contains pg_catalog | @> | polygon | polygon| boolean | contains pg_catalog | @> | tsquery | tsquery| boolean | contains public | @> | hstore| hstore | boolean | public | @> | text[]| text[] | boolean | text array contains compared by strict (15 rows) This query works fine when adding cast to text[]: ts=# SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns, (stxkind @> '{d}'::text[]) AS ndist_enabled, (stxkind @> '{f}'::text[]) AS deps_enabled FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259' ORDER BY 1; oid | stxrelid | nsp | stxname | columns | ndist_enabled | deps_enabled -+--+-+-+-+---+-- (0 rows) Is this to be considered an issue with parray_gin or with psql ? I don't think that's an urgent problem to fix, but if someone has a workaround for \d I would appreciate if you'd pass it along :) Thanks in advance Justin -- 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] Re: Restoring tables with circular references dumped to separate files
On Sat, Oct 21, 2017 at 10:48 PM, doganmeh wrote: ... > On another note, I used to take full backups (entire database), however > switched to table by table scheme in order to make it more VCS friendly. > Namely, so I only check into github the dumps of the tables that are updated > only. > So, from that perspective, is there a dump-restore scenario that is widely > used, but is also VCS friendly? To my knowledge, pg_restore does not restore > backups that are in "plain text" format, and compressed formats such as > "tar" would not be github friendly. Not widely used, but you have the directory format ( disclaimer: have not tested it for VCS friendliness ). It populates a directory similar to what uncompressing a tar format would, but I do not know if it renames the files from run to run, but should be easy to test. Also note it is documented as compressed BY DEFAULT, but you can use options to avoid compression, and it is the only one which supports paralell dumps. Also, custom and tar can be made uncompressed, but I do not think that's a great idea. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9.6 fails to start on VMWare
I’ve migrated a running Debian Jessie system from a Google Compute instance to a VMWare ESXi 6.5 system. Postgres won’t start, although returns [ok] : /etc/init.d/postgresql start 9.6 [ ok ] Starting postgresql (via systemctl): postgresql.service. 2017-10-22 14:08:28 UTC [2479-1] LOG: 0: database system shutdown was interrupted; last known up at 2017-10-22 14:07:20 UTC 2017-10-22 14:08:28 UTC [2479-2] LOCATION: StartupXLOG, xlog.c:6009 2017-10-22 14:08:28 UTC [2479-3] LOG: 0: database system was not properly shut down; automatic recovery in progress 2017-10-22 14:08:28 UTC [2479-4] LOCATION: StartupXLOG, xlog.c:6505 2017-10-22 14:08:28 UTC [2479-5] LOG: 0: redo starts at A1/688398C0 2017-10-22 14:08:28 UTC [2479-6] LOCATION: StartupXLOG, xlog.c:6760 2017-10-22 14:08:28 UTC [2479-7] LOG: 0: invalid record length at A1/6AC96408: wanted 24, got 0 2017-10-22 14:08:28 UTC [2479-8] LOCATION: ReadRecord, xlog.c:4024 2017-10-22 14:08:28 UTC [2479-9] LOG: 0: redo done at A1/6AC963E0 2017-10-22 14:08:28 UTC [2479-10] LOCATION: StartupXLOG, xlog.c:7023 2017-10-22 14:08:28 UTC [2479-11] LOG: 0: last completed transaction was at log time 2017-10-21 16:40:52.629875+00 2017-10-22 14:08:28 UTC [2479-12] LOCATION: StartupXLOG, xlog.c:7028 2017-10-22 14:08:29 UTC [2479-13] LOG: 0: request to flush past end of generated WAL; request A1/72AF47A8, currpos A1/6AC96408 2017-10-22 14:08:29 UTC [2479-14] CONTEXT: writing block 0 of relation base/203725/2840_vm 2017-10-22 14:08:29 UTC [2479-15] LOCATION: WaitXLogInsertionsToFinish, xlog.c:1583 2017-10-22 14:08:29 UTC [2479-16] FATAL: XX000: xlog flush request A1/72AF47A8 is not satisfied --- flushed only to A1/6AC96408 2017-10-22 14:08:29 UTC [2479-17] CONTEXT: writing block 0 of relation base/203725/2840_vm 2017-10-22 14:08:29 UTC [2479-18] LOCATION: XLogFlush, xlog.c:2765 2017-10-22 14:08:29 UTC [2478-1] LOG: 0: startup process (PID 2479) exited with exit code 1 2017-10-22 14:08:29 UTC [2478-2] LOCATION: LogChildExit, postmaster.c:3504 2017-10-22 14:08:29 UTC [2478-3] LOG: 0: aborting startup due to startup process failure 2017-10-22 14:08:29 UTC [2478-4] LOCATION: reaper, postmaster.c:2777 2017-10-22 14:08:29 UTC [2478-5] LOG: 0: database system is shut down 2017-10-22 14:08:29 UTC [2478-6] LOCATION: UnlinkLockFiles, miscinit.c:755 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get login user name and host name in pgaudit
I installed latest pgaudit (1.2) with pg10. I am testing it and I see that it does not log the login user name and host name. For example, if user mary is running select * from sensitive_table, I want Mary and the machine from where she ran in the log. It seems to log the ids which needs to be joined with pg_ views to convert it into login user name and host name. any pointers on how to get it done. thanks. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- 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] Weird performance difference
On Sat, Oct 21, 2017 at 9:38 AM, Tom Lane wrote: > Also try explicitly ANALYZE'ing the foreign tables. I do not > believe auto-analyze will touch foreign tables ... Autovacuum and autoanalyze only process relations and matviews, discarding the rest when scanning pg_class. See do_autovacuum(). -- Michael -- 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] A question on pg_stat_subscription view
Hello, You can find more information about that view and its columns. https://www.postgresql.org/docs/devel/static/monitoring-stats.html#pg-stat-subscription Regards, Gunce On 22 Oct 2017 Sun at 11:11 Önder Kalacı wrote: > Hi, > > I'm trying to understand the view pg_stat_subscription. What is the > `latest_end_lsn` column? Is that the latest lsn flushed or lsn replied or > something else? > > Thanks! > -- Gunce Kaya
[GENERAL] A question on pg_stat_subscription view
Hi, I'm trying to understand the view pg_stat_subscription. What is the `latest_end_lsn` column? Is that the latest lsn flushed or lsn replied or something else? Thanks!