Re: [GENERAL] Surprising results from array concatenation

On Tue, Apr 25, 2017 at 12:53 PM, Tom Lane wrote: > Yeah. The core problem here is that the parser has to disambiguate the > || operator: is it "anyarray || anyelement" or "anyarray || anyarray"? > ​<...>​ > Peeking at the contents of the literal would make the behavior

[GENERAL] Surprising results from array concatenation

array constant seems surprising. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RRD* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrd

[GENERAL] vacuum - reclaiming disk space.

I have a large table with numerous indexes which has approximately doubled in size after adding a column - every row was rewritten and 50% of the tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot seem to finish within the scheduled downtime. Any suggestions for reclaiming

[GENERAL] pl/pgsql trigger function - compare *most* columns in NEW vs. OLD

Is there a simple notation for comparing most columns in the new and old records in a pl/pgsql trigger function? Something like (new.b, new.c, new.d) = (old.b, old.c, old.d) works to compare all the columns except 'a', but is fragile in that it needs to be updated any time a column is added to

[GENERAL] Vacuum freeze

check_postgres.pl (--action=autovac_freeze) recently complained that we needed to run VACUUM FREEZE. Doing so generated a boatload of WAL files - perhaps on the order of the of the database itself. Is VACUUM FREEZE something that is normally handled by autovac? If so, how would we approach

[GENERAL] Estimating WAL usage during pg_basebackup

I need to get an idea of how much WAL space will be required during a long (many hours) pg_basebackup over a relatively slow network connection. This is for a server that's not yet running PITR / streaming. Any thoughts? * mike.blackw...@rrd.com*

[GENERAL] EBCDIC conversion

We have a need to check certain text fields to be sure they'll convert properly to EBCDIC. A check constraint with a convert() was the initial thought, but there doesn't seem to be a default conversion from UTF8 to EBCDIC. Does anyone have an implementation they'd care to share, or suggestions

[GENERAL] EF / npgsql and VIEWs

I understand from one of our developers there may be issues using VIEWs with Entity Framework and npgsql. Can anyone with some experience using PostgreSQL in a .NET environment comment? __ *Mike Blackwell | Technical

[GENERAL] Syntax problem with INDEX on expression

://www.example.com']]))[1]::text)... ^ It looks like it doesn't like the array subscript. What might I be missing? __ *Mike Blackwell | Technical Analyst, Distribution

Re: [GENERAL] Syntax problem with INDEX on expression

So, one set to mark the parameter and one for the expression? It's starting to look like Lisp. ^_^ Thanks! __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750

[GENERAL] Enforcing minimum on many-to-many relationship?

I have a pair of tables, and a third describing a many-to-many relationship between them. Along the lines of: CREATE TABLE a (a_id integer NOT NULL PRIMARY KEY); CREATE TABLE b (b_id integer NOT NULL PRIMARY KEY); CREATE TABLE x (a_id integer NOT NULL REFERENCES a(a_id) ON DELETE CASCADE, b_id

Re: [GENERAL] Check table storage parameters

Try pg_class.reloptions? __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http

Re: [GENERAL] unique constraint with significant nulls?

Ah. A pair of constraints. I see. Thanks! __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818

[GENERAL] unique constraint with significant nulls?

__ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com

Re: [GENERAL] unique constraint with significant nulls?

Interesting, but that assumes there's a value to use in the coalesce that isn't a valid data value. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave

Re: [GENERAL] Revoking table function creation privileges

row) __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com

[GENERAL] Log: Untranslatable character: no DETAIL

These are two separate databases running on the same server (postgresql instance). __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174

[GENERAL] Subselect with incorrect column not a syntax error?

Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike -- Sent via

Re: [GENERAL] Subselect with incorrect column not a syntax error?

Indeed. __ Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com

[GENERAL] PITR backup - estimating size

I'd like to switch to PITR backups, but have limited disk space. Is there a way to get a ballpark estimate by monitoring a running system, without actually creating the WAL files and risking filling a filesystem? Mike

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

__ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com* On Wed, Mar 14, 2012 at 16:04

Re: [BUGS] [GENERAL] Altering a table with a rowtype column

Not a bad idea. I'd need to convert existing data, but it'd be an excuse to try out hstore. ^_^ Mike * mike.blackw...@rrd.com* On Thu, Mar 8, 2012 at 11:08, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Mar 7, 2012 at 2:49 PM, Merlin Moncure mmonc...@gmail.com wrote: On a practical

[GENERAL] Altering a table with a rowtype column

Given a pair of tables: create table a ( id serial, stuff text, more_stuff text ); create table a_audit ( id serial, old_record a, new_record a ); How can one alter the structure of table a? Attempting ALTER TABLE a ADD COLUMN even_more_stuff text; results in the message:

Re: [GENERAL] Altering a table with a rowtype column

works for me -- what version are you on? merlin -- [wcs1459@aclnx-cisp01 ~]$ psql --version psql (PostgreSQL) 9.1.1 contains support for command-line editing [wcs1459@aclnx-cisp01 ~]$ cat x create table a ( id serial, stuff text, more_stuff text ); create table

Re: [GENERAL] Does the current user have UPDATE privilege on FOO?

On Mon, Feb 27, 2012 at 11:00, Lionel Elie Mamane lio...@mamane.lu wrote: Hi, I'm trying to understand the clean way to determine whether the current role has UPDATE (or SELECT or DELETE or UPDATE) privileges on a specific table (or column). If I can do it in a way that is portable across

[GENERAL] Orphaned temp table

mydb 2012-02-27 13:05:35 CST [18400]: [2-1] @ LOCATION: do_autovacuum, autovacuum.c:2022 __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles

[GENERAL] 9.1.1 crash

The following are the relevant log entries from a recent crash of v9.1.1 running on an older RHEL Linux box. This is the first crash we've experienced in a lot of years of running Pg. Any assistance in how to determine what might have caused this is welcome. -- 2012-02-10 13:55:59 CST [15949]:

Re: [GENERAL] How To Handle Hung Connections

the applications/users attempting to access the database, to avoid them grabbing another connection while I'm typing. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave

[GENERAL] disable autovacuum per-database?

I'd like to temporarily disable autovacuum on a single database while it is being loaded. Is there an easy way to do this? __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR

[GENERAL] Selective backup script

have using up time/disk for backup. Might there be a way to tag those databases somehow so the backup script knows to skip them? I'd rather not hard code the list in the script. Thoughts? __ *Mike Blackwell

Re: [GENERAL] Selective backup script

database level metadata' field somewhere. ^_^ Thanks, __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818

Re: [GENERAL] stored function data structures - difficulty

. I have use the system catalogs for several one time projects related to foreign keys, including checking which fks have associated indexes defined. __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout

Re: [GENERAL] SIGNALNAME in pg_ctl kill

The manual section on the postmaster process has some info: http://www.postgresql.org/docs/current/static/app-postgres.html __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR

Re: [GENERAL] All and ANY

http://www.postgresql.org/docs/9.1/static/functions-subquery.html __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office

[GENERAL] Cleaning up index names

? __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com

[GENERAL] SQL-ASCII database cleanup

I have an older database that was created with SQL-ASCII encoding. Over time users have managed to enter all manner of interesting characters, mostly via cut and paste from Windows documents. I'm attempting to clean up and eventually the database to UTF8. I've managed to find most of the data

[GENERAL] Using meta-data for foreign key?

I have an existing table in an app, along the lines of: CREATE TABLE foo ( name text, address text, some_numeric_info integer, a bunch of additional fields here ); I now need to be able to associate additional information (e.g. printing order) with each field. Is it a bad idea to use

Re: [GENERAL] Using meta-data for foreign key?

More detail, as suggested. I have an existing table in an app, along the lines of: CREATE TABLE foo ( name text, address text, some_numeric_info integer, a bunch of additional fields here ); I essentially need another table CREATE TABLE foo_printing_options ( field_name text,