Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

otential as, hopefully, people will > check first, but I can imagine a hosting provider or environments where > there are lots of independent clusters not catching this issue in their > testing, only to discover someone set their database to 'datallowconn = > false' for whateve

Re: [GENERAL] Postgres and data warehouses

get some brief feedback on whether I'm on the > wrong track or not, and if there's some better approach I should be looking > at first > instead. Consider if new Logical Change Set features of 9.4 might apply to your case. May accomplish same as trigger based solution with

Re: [GENERAL] Strange security issue with Superuser access

LINE 1: SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a" OPERATOR(p... ^ QUERY: SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x HTH > > David J. > ​ >   > --

Re: [GENERAL] check data for datatype

e foo alter a type numeric using foo(a); select * from foo; --now go delete your 'nan rows abort; > > Thanks, > -Suersh Raja > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list

Re: [GENERAL] pg_dump permssion denied problem

ELSE min_value END AS min_value, cache_value, is_cycled FROM > thingy_thingy_id_seq > > But in psql, \dt and \ds show that I own all my tables and sequences, and > of course I can run the SELECT statement above with no problem, as in the > referenced > thread. &g

Re: [GENERAL] pg_upgrade problem

t;" TO ""; > GRANT "" TO ""; > > Doing a pg_dumpall -g on the database produces the same result. Well then I don't presume this is a pg_upgrade issue. Inspect your pg_auth_members catalog for entries referring to rows absent from pg_authid. Did some

Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

(kbytes, -s) 10240 >> cpu time (seconds, -t) unlimited >> max user processes (-u) 16384 >> virtual memory (kbytes, -v) unlimited >> file locks (-x) unlimited >> >> >> Thanks >> Prabhjot &g

Re: [GENERAL] Postgresql 9.2 has standby server lost data?

15-06-18 01:12:41 UTC: >> LOG: 0: invalid record length at 868/112AFB00 >> >> 2015-06-18 01:12:44.490 UTC::@:[28168]:2015-06-18 01:12:41 UTC: >> LOCATION: ReadRecord, xlog.c:4078 >> >> 2015-06-18 01:12:44.490 UTC::@:[28166]:2015-06-18

Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function

variable name that could be used instead.  > I guess I mis-remembered... Try this... sj$ psql -eqf q begin; create table foo as select 'here goes some text'::text as tf; create function foo () returns text as $$ begin return case when true then tf end from foo limit 1; end $$ l

Re: [GENERAL] Run analyze on schema

foo('public'); select foo('pg_catalog'); -- Enjoy!! > > Thanks, > -Suresh Raja > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresq

Re: [GENERAL] very slow queries and ineffective vacuum

what might be the problem? I tried > pg_badger, but all > I got were specific queries and their times, but the long query > times are just one of the symptoms of what's wrong with the database, not the > cause. > > Perhaps I'm miss

Re: [GENERAL] PostgreSQL Developer Best Practices

we got the check, but it still took them six months to own up to it. DOH! Next time a screwball outfit sends you a check for $22k erroneously just go deposit it :-) > -- > Adrian Klaver > adrian.kla...@aklaver.com -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.cons

Re: [GENERAL] UPDATE an updatable view

If you want to > check whether the value has not been changed then: >> >> IF NEW.empname = OLD.empname THEN > > That's exactly the solution I hit on. Back to work, and thanks again. > >> >>> >>> Thanks! >> >> >> >> -- >&g

Re: [GENERAL] Hiding name and version

how server_version or server_version_num; Oh, and interactive psql without -q prints server version in the banner :-) > > regards, tom lane -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pg

Re: Ynt: [GENERAL] How to drop user if objects depend on it

s 2 data > schemas: public and firma1. User may have directly or indirectly assigned > rights in this >database and objects. User is not owner of any ob... > > >Devam?n? okuyun... >

Re: [GENERAL] psql connection option: statement_timeout

-c option or included in a file with -f . > Note the exceptions that can be specified per user as Scott mentioned. No one has mentioned the PGOPTIONS variable here yet ?... FWIW > Melvin Davidson > I reserve the right to fantasize.  Whether or not you > wish to share my fantasy

Re: [GENERAL] unique constraint with several null values

hat breaks when the application is multi-threaded > and the rules are not applied at the database level. > > Another solution I can think of is to just use a trigger to prevent the > duplicate rows.   > > Any thoughts are certainly appreciated.  I can't do mu

Re: [GENERAL] Uber migrated from Postgres to MySQL

done several ~7TB pg_upgrades and with the hard link option and a framework that parallelizes the post-analyzer phase... ...45 minutes till completion. > > Regards, > Hristo S. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Uber migrated from Postgres to MySQL

p://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800

Re: [GENERAL] dumping table contents in a sensible order

_one is self-referential by > way of a parent_id field. > > How can I either: > > - dump the table in an insertable order? > - have the load only apply the foreign key constraint at the end of > each table import? > > cheers, > > Chris -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] vacuum of empty table slows down as database table count grows

m my test run (i've been plotting it in > grafana via carbon) > > Is this a known issue? Can i do anything to improve performance here? > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing

Re: [GENERAL] Why does this hot standy archive_command work

cessfully, to a command > that actually archives logs just requires a reload. So this lets you enable > archiving without halting the server by changing the command. > > Or that's how I vaguely recall it working some years ago. Things may have > changed now - you're followi

Re: [GENERAL] Why does this hot standy archive_command work

"bto...@computer.org" writes: > - Original Message ----- >> From: "Jerry Sievers" >> To: "Steve Atkins" >> Cc: "pgsql-general" >> Sent: Monday, January 23, 2017 12:52:46 PM >> Subject: Re: [GENERAL] Why does this

Re: [GENERAL] pg_dump Conflict with recovery

ster > Systems Analyst II > Ravn Alaska > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7293 > --- > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.780

Re: [GENERAL] Postgresql Dump Question

lease read the Pg documentation section on pg_dump which is only about 4400 words. Probably takes you 30 minutes to get through it and a few more hours to hands-on test for even better understanding. Very likely what the test requires will be learned this way. Good luck! > Regards, > SS > --

Re: [GENERAL] Regexp_replace question / help needed

t; > Would anyone here point me in the right direction? > select regexp_replace('http://foo/wow/blah/zzz.php', '/([^/]*)$', '&file=\1'); regexp_replace -- http://foo/wow/blah&file=zzz.php (1 row) > > Thanks!

Re: [GENERAL] Loggingt psql meta-commands

es and filter for accesses to the > pg_catalog > > -- > john r pierce, recycling bits in santa cruz > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: >

Re: [GENERAL] grant connect on database

revoke it from public.. Connect is granted to public by default. > Thanks > > > > -- > View this message in context: > http://postgresql.nabble.com/grant-connect-on-database-tp5877872.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. --

Re: [GENERAL] Catalog bloat (again)

as an unprivileged user and thus can't vacuum catalogs... perhaps with a naive batch job launcher that sends stderr to /dev/null. > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Privileges granted on dblink extension function do not survive database dump and restore

gt;    pg_dump. Such a change is usually only sensible if you concurrently >    make the same change in the extension's script file." > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises &

[GENERAL] Pg-Logical output pkg; can't install 9.4 and 9.5 on same Wheezy box

/postgresql/9.5 /usr/lib/postgresql/9.5/lib /usr/lib/postgresql/9.5/lib/pglogical_output.so /usr/include /usr/include/postgresql /usr/include/postgresql/pglogical_output /usr/include/postgresql/pglogical_output/compat.h ^^ /usr/include/postgresql/pglogical_output/hooks.h -- Jerry S

Re: [GENERAL] How to quote the COALESCE function?

; shouldn't quote it? Is it instead a keyword, as described in > the "Lexical Structure" section of the docs [2]? How can I find > out which other functions are not meant to be quoted? > > I'm aware that I do not need to quote the `coalesce` and `upper` > functions and I m

Re: [GENERAL] pg_basebackup: return value 1: reason?

starting up > 2016-04-15 23:35:02 CEST:@:[28926] LOG:  entering standby mode > 2016-04-15 23:35:02 CEST:@:[28926] LOG:  database system was not properly > shut down; automatic recovery in progress  <this means something > missing from > pg_basebackup > 2016-04

Re: [GENERAL] Postgres processes getting stuck (bug?)

iveLock" (the only record there with a locktype of > "virtualxid"), a few have the "RowExclusiveLock" mode and the vast majority > have the "AccessShareLock" > mode. > > Thanks, > Ciprian > -- Jerry Sievers Postgres DBA/Development C

Re: [GENERAL] Aborted pg_dump run empties existing archive file

other utility truncing files a bit prematurely but it was long ago. But it can't know beyond that point if something else is going to cause the backup to abort. And I would not expect pg_dump to create a new file alongside your old file just in case since this can so easily be implemented

Re: [GENERAL] Join field values

/ result must be varchar. No sense in writing your own func for this; the feature is already provided. select array_to_string(array(select * from generate_series(1,5)), ','); array_to_string - 1,2,3,4,5 (1 row) > Help to write SELECT-query for this task. --

[GENERAL] Adding domain type with CHECK constraints slow on large table

from being evaluated. In our case, this hackery would save hours of downtime on a prod system. Comments? PS: Sent this a few hours ago and never saw it. Sorry if duplicate. -- ------- Jerry Sievers 732 365-

Re: [GENERAL] Adding domain type with CHECK constraints slow on large table

Tom Lane <[EMAIL PROTECTED]> writes: > Jerry Sievers <[EMAIL PROTECTED]> writes: > > > I believe what's happening here is that the server doesn't realize > > that the new column is going to have all nulls and that the check > > constraint allows

[GENERAL] Easier string concat in PL funcs?

ma.% where % do whatever ; $$, array [ v_tablename, v_rulename, v_tablename, v_conditions ], '%' ); -- ...Still not exactly simple, I realize :-) Thanks -------

[GENERAL] Perhaps silly question about empty search_path

;" does not exist [EMAIL PROTECTED] = \c jerry You are now connected to database "jerry". [EMAIL PROTECTED] = show search_path; search_path - "" (1 row) [EMAIL PROTECTED] = -- ------

Re: [GENERAL] Perhaps silly question about empty search_path

Jerry Sievers <[EMAIL PROTECTED]> writes: > Hello; > > To begin with, I'm not certain how useful it would be... that said; it > seems odd that a role or DB can have it's config search_path set to > empty string but you can't explicitly set it that way

Re: [GENERAL] What could keep a connection / query alive?

ng in pg_stat_activity. SIGTERM on such a backend will probably also fall on deaf ears. This has been my experience several times in an environment of EnterpriseDB 8.2 systems on Solaris 10. YMMV > -- Jerry Sievers e: gsiever...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mai

Re: [GENERAL] Autocommit off - commits/rollbacks

>> >> Why does postgres rollback the whole transaction after an error? > > It's a PostgreSQL limitation (or, arguably, optimization). When a Well, any transactional RDBMS whatsoever should behave that way. Hardly a PostgreSQL exclusive feature :-) -- Jerry Sievers e: gsiever..

Re: [GENERAL] stack depth limit exceeded

n. I want to get > around this issue without changing the server configuration [snip] I'd strongly suspect a case of infinite recursion. Have you ruled that out first?> You might try incrementing a sequence in the function during one of the bad runs to see how deep it's recurs

Re: [GENERAL] Access to NEW.column outside of a trigger function.

read (not write, just read) the > NEW.column values that a trigger function would normally have access > to? Sure. Just pass them into your validator func as parameters. But why are you avoiding use of a trigger here? > Thanks in Advance for any help. > -- Jerry Sievers Postgre

Re: [GENERAL] Sequence names have 64 character limit?

predictable sequence naming convention of the PG default > __seq. > > Yes, I know that its not a great idea to depend on consistent naming > conventions! > > Carlo > -- Jerry Sievers Postgres DBA/Development Consulting e: gsiever...@comcast.net p: 305.321.1144 -- Sent via pgs

Re: [GENERAL] Sequence names have 64 character limit?

y that finds it by > rendering the text to regclass to an OID... but this stuff makes me nervous. Nervous or otherwise, if your sequences are owned by the tables either by their implicit creation with SERIAL keyword or a later ALTER SEQUENCE OWNED BY, then querying the class and depend catalogues

Re: [GENERAL] Table lock while adding a column and clients are logged in

ning transactions in your application. > cheers sven > -- > Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir > belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de -- Jerry Sievers e: gsiever...@comcast.net p: 305.321.1144 -- Sent via pgsql-general ma

Re: [GENERAL] Table lock while adding a column and clients are logged in

Jerry Sievers writes: > "Sven Haag" writes: > >> hello pg fans, >> >> we have an application that communicates via ODBC directly to the >> postgres database. >> >> if i'm trying to add an additional column to a table in pgadmin while &g

Re: [GENERAL] Dumping functions with pg_dump

to target a specific > function? Just do; pg_dump --schema-only Go find the function definition in the output script, snip it out and load with psql. Remember to load it into the correct schema and whatever other details. HTH -- Jerry Sievers e: gsiever...@comcast.net p: 305.321.1144 -- Sent v

Re: [GENERAL] Auto Adjust Age

r enough to see what triggers > and views are so perhaps it's just my level with SQL in general. I'd suggest not storing age but instead wrapping with a view that calls date_trunc('year', now() - dob). If you must store the age then you have to run a daily batch to make the bul

Re: [GENERAL] Deferred foreign key constraint downsides

fault should be avoided. Why promote being able to insert rows in related tables using other than top-down sequencing? Sure, if you have an existing app that does that, which you can't change, go with deferred validation, otherwise no. YMMV > Thanks. > > -- > Jack Christensen &

Re: [GENERAL] archiving transaction log file failed too many times, will try again later

failed with exit code 1 > 2014-05-23 10:58:57 CEST DETAIL: The failed archive command was: false > 2014-05-23 10:58:58 CEST LOG: archive command failed with exit code 1 > 2014-05-23 10:58:58 CEST DETAIL: The failed archive command was: false > 2014-05-23 10:58:58 CEST WARNING: archiving

Re: [GENERAL] Downtime-free 'alter table set tablespace'

y' option) ? Not low level that I'm aware of. But I'd suggest you explore Slony or Skytools for doing a logical replica of the table that needs moving and this will get you through it with very low downtime or possibly none if you're clever and careful. Learning how to configure an

Re: [GENERAL] Postgresql 9.2.4 - timezone error

lrwxrwxrwx 1 yomama yomama 3 May 29 11:38 bar -> foo lrwxrwxrwx 1 yomama yomama 3 May 29 11:38 foo -> bar > > Thanks > > Bhushan Pathak > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] what does pg_activity mean when the database is stuck?

in > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.c

Re: [GENERAL] Alternative to psql -c ?

pg2, sys conn = psycopg2.connect(...) cur = conn.cursor( cur.execute(sys.stdin.read()) conn.commit() HTH) PS: Complex multi-statement executor calls are somewhat nuanced in their own ways and I would be trying hard *not* to do this without very good reason. > Regards, > James -- Jerry Sie

Re: [GENERAL] how to create a role with no privileges?

ce such a role would not correspond to any real role.  > Its purpose, rather, is to serve as the starting point for defining more > realistic roles by > selectively adding the fewest privileges possible). > > TIA for any pointers! > > kynn > -- Jerry Sievers Postgre

Re: [GENERAL] Best way to list a roles owned objects?

ownership of the depending objects. This makes possible for you to easily identify such items later rather then have them mixed up with everything postgres owns. The assumption is, that many of the things so reassigned are quite possibly junk, given that the real owner has been dropped

Re: [GENERAL] Best way to list a roles owned objects?

Jerry Sievers writes: > Felipe Gasper writes: > >> On 7/1/14 1:13 PM, John R Pierce wrote: >> >>> On 7/1/2014 11:08 AM, Felipe Gasper wrote: >>>> What is the best way to list a role’s owned objects in any database? >>> >>> query pg

Re: [GENERAL] Quering complete PLPGSQL code

Néstor Boscán writes: > Hi > > I want to get the PLPGSQL code from the PostgreSQL 9.1 database. I've used > pg_proc that only gives me the body of the code. Is there a Postgres function > that can build > all the code? pg_get_functiondef(oid) > > Regards, >

Re: [GENERAL] Restart replicated slave procedure

a configurable number of WAL segments back and buy some more time till you have to resync the stand bys. Setting archive_command to '' or something like '/bin/false' lets you delay archiving forever till you change them back again and/or fill whatever file system pg_xlog wri

Re: [GENERAL] Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...

already pending... if there are longer running complex transactions involved. Suppose... session A begin; insert into your table key=1 ... do more work here... meanwhile... session B test for row and I don't see it try insert and hang here till commit/abort of session A session A commit ses

Re: [GENERAL] Issue with to_timestamp function

> > But, when I select data from the table sql_log_import, I get an error: > > -- Executing query: > > select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time zone as > tStamp > > from sql_log_import > > where id <= 10 > > *

Re: [GENERAL] CREATE SYNONYM in PostgreSQL

plements this and many other Oracle look allike features. HTH > > > > > - > Regards, > Vinayak, > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/CREATE-SYNONYM-in-PostgreSQL-tp5818446.html > Sent from the PostgreSQL - gene

Re: [GENERAL] cannot drop user

s_id_seq > owner of table auth_group_permissions > owner of sequence auth_group_id_seq > owner of table auth_group > owner of table applications > owner of table alarmdevents > > The tugdb database is gone but these artifacts are all from it. How is that > possible if t

Re: [GENERAL] Temporarily suspend a user account?

org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- > Melvin Davidson > I reserve the right to fantasize.  Whether or not you > wish to share my fantasy is entirely up to you. [01] > -- Jerry Sievers Postgres DBA/Develo

Re: [GENERAL] Temporarily suspend a user account?

for > the role in question. Here we go... disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; > > David J. > ​ >   &g

Re: [GENERAL] Temporarily suspend a user account?

on and a session registering in pg_stat_activity. > ​David J. > > -- > View this message in context: Re: Temporarily suspend a user account? > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > -- Jerry Sievers Postgr

Re: [GENERAL] stored procedure variable names

ables (ex: p_user_id) makes the application code harder to > write as we have a lot of dynamic code that is expecting "user_id" instead of > "p_user_id". > > Is there any plan to add a character to differentiate between variables? Not that I'm aware of but ple

Re: [GENERAL] Triggers and scalability in high transaction tables.

st like 2 or more sessions trying to update the same row, you are going to single thread around such an operation like it or not. You need to tell us a lot more about your problem and what the triggers do. > Thanks. > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult..

Re: [GENERAL] [ADMIN] Unable to reload postgresql.conf without restarting

re: > -effective_cache_size? > -work_mem > > Im using posgres 9.1 on Centos Linux (amazon ec2) > > Thanks > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgr

Re: [GENERAL] Discerning when functions had execute revoked from public

g.pg_proc (or any other table for that matter). Is > there a way to find this somewhere in the catalog? > > Apologies if this should be obvious. I'm sure I will find it as soon as > I hit send. :-) > > thanks, > -Todd > > > -- > Sent via pgsql-general mailin

Re: [GENERAL] psql copy from through bash

n though. HTH > Thanks in advance > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consul

Re: [GENERAL] restoring to different architecture with WAL

22:11:16 CET FATAL: > ?database files are incompatible with server > 2013-03-07 22:11:16 CET DETAIL: ?The database cluster was initialized with > USE_FLOAT8_BYVAL but the server was > compiled without USE_FLOAT8_BYVAL. > 2013-03-07 22:11:16 CET HINT: ?It looks like you need to recom

Re: [GENERAL] create temporary table problem

varchar(32) ); > ERROR:? could not create directory "pg_tblspc/16385/PG_9.0_201008051/20304": > No such file or directory > > The same query work in PGSQL 8.0 > > Please guide me. > > Regards, > J Prasanna Venkatesan > -- Jerry Sievers Postgres DBA/Developmen

Re: [GENERAL] Facing difficulty in PITR

ere spinning while you figure out where the missing WALs are and move them into place. HTH > Thanks in advance.Any?suggestions?would be?appreciated. > > Regards, > Chiru > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 --

Re: [GENERAL] Save postgresql node to catch only DML except delete queries

Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@com

Re: [GENERAL] SELECT count(*) differs from result in pgadmin

, 2873175 estimated total rows > Query returned successfully with no result in 2611 ms. > > -- > > ...So it would seem that PGAdmin is correct, but why am I getting the > wrong number from SELECT Count(*)? > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Trigger function on Warm Standby

-+--- > ?? 19218 | processnodeafter | A > (1 row) > > Can someone tell me if the trigger will never happen on the Standby server? > Is it expected or am I missing some other > settings? It will not fire on a standby. > Thanks~ > Ning > -- Jerry Sieve

[GENERAL] Dump/Reload pg_statistic to cut time from pg_upgrade?

of type anyarray CONTEXT: COPY pg_statistic, line 1, column stavalues1: "{_assets,_income,_liabilities,assets,income,liabilities}" Setting allow_system_table_mods to 'on' is required also prior to attempting the import. Has anyone else attempted anything similar? Any feedbac

Re: [GENERAL] TOC errors

options Or even possibly you are restoring a data-only dump from a non-constrained origin system into one with unique constraints. HTH > > Thank you, > Perry > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgs

Re: [GENERAL] pg_stat_replication became empty suddenly

uccessfully connected to primary > FATAL: could not receive data from WAL stream: FATAL: requested WAL segment > 000100120005 has already Raise wal_keep_segments on your master configs ,and HUP and/or start your standby a lot sooner after it's reloaded. > -- Jer

Re: [GENERAL] OLAP

tables in PG or a >>> setup like that. >>> I've seen an old reference to "Cybertec OLAP", but they don't seem to carry >>> a product like that if I watch their site. >>> >>> I'm looking for suggestions for something that would

Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.

ff > - Could you quickly describe your workload? warehouse with user self-service reporting creation/storage allowed in same system. > > Feel free to add any information you think is pertinent ;) Great idea!! Thanks > > Greetings, > > Andres Freund -- Jerry Sievers Pos

Re: [GENERAL] Error in PostgreSQL Log

TEMENT:  ROLLBACK PREPARED '131077_AP// > CqqfIep65ipZAmdpAAKVDjE=_AP// > CqqfIep65ipZAmdpAAKVFQAA' > ERROR:  prepared transaction with identifier "131077_AP// > CqqfIep65ipZAmdpAAK4bDE=_AP// > CqqfIep65ipZAmdpAAK

Re: [GENERAL] Error when building new db using pg_restore

ot; > < 2017-06-16 21:26:01.625 PDT >WARNING: no privileges were granted > for "public" > .. > > So, these errors don't appear to cause problems on the target database > (rdev1), but it might be > an issue if we tried to do more things with PL/pgSQL. Is it

Re: [GENERAL] Download 9.6.3 Binaries

for best results. HTH > Thanks, > > > Igal Sapir > Lucee Core Developer > Lucee.org > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Chained slaves smaller?

after something $interesting happened... and pg_basebackup ignores it. Pls let us know what you find. HTH > This makes no sense to me since I thought SR replicas are bit for bit copies, > so I’m somewhat concerned. Any ideas how this could be? > — > Jon Erdman > Postgres Zealot --

Re: [GENERAL] I can't cancel/terminate query.

ient if you can locate it. If this is a no-can-do, there's a way to forge a network packet to close the blocked socket which you'll have to Google for if interested. Else you will have to shutdown -m immediate. Sorry. >   > > > -- > [uc] > > -- Jerry Sievers

Re: [GENERAL] What is exactly a schema?

gt;> Could some tables (in the public schema) be shared among all the >>> schemas? >>> >>> >> >> Yes and yes. In the Postgresql world, the word "schema" is maybe >> unfortunately overloaded, but whenever you read it think >> "names

Re: [GENERAL] Two-phase commit case studies

ly due to some interesting potential conflicts. > > Thanks -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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 transaction started always in WRITE mode.

t. Wenn Sie nicht der vorgesehene > Adressat dieser E-Mail oder dessen Vertreter sein sollten, so > beachten Sie bitte, dass jede Form der Kenntnisnahme, > Veröffentlichung, Vervielfältigung oder Weitergabe des Inhalts dieser > E-Mail unzulässig ist. Wir bitten Sie, sich in diesem Fall mit dem > Absender der E-Mail in Verbindung zu setzen. > > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Logging failed connections

Stephen Cook writes: > Hello! > > When a client gets the error message about "remaining connection slots > are reserved for non-replication superuser connections", is this logged? > What should I be grep-ing for? Grep for the token FATAL in your logs. > > Than

Re: [GENERAL] Selecting some schema not suported for libpq PQconnectdbParams

the > default schema for some database? > > Of course, I'm asking this because the schema to use will not be the > "current user" nor "public". .pg_service.conf... [foo] host=1.2.3.4 port=1234 dbname=groovydb options=-c search_path=your_schema_of_choice,some_mo

[GENERAL] Up to date conventional wisdom re max shared_buffer size?

scheduled downtime so this and other settings requiring a full restart are not touched without good reason. Thanks -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Thanks Andres! See inline... Andres Freund writes: > Hi, > > On 2017-09-19 17:00:05 -0500, Jerry Sievers wrote: >> Briefly, just curious if legacy max values for shared_buffers have >> scaled up since 8G was like 25% of RAM? > > It's very workload dependent

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

Ron Johnson writes: > On 09/19/2017 05:00 PM, Jerry Sievers wrote: > [snip] > >> The DB is 10TB total size with OLTP plus some occasional heavy batching >> which frequently correlates with degradation that requires intervention. >> >> Unrelated server problem fo

[GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

2017 x86_64 x86_64 x86_64 GNU/Linux -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

Scott Marlowe writes: > On Wed, Sep 20, 2017 at 12:14 PM, Jerry Sievers > wrote: > >> Basically as per $subject. >> >> We took a perf hit moving up to newer hardware and OS version which >> might in some cases be OK but admittedly there is some risk running

  1   2   >