[GENERAL] decoding BLOB's

Hi All; we've found a post about dumping blobs: / / /I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded by single quotes. b) *that won't

Re: [GENERAL] decoding BLOB's

On 02/22/2016 11:08 AM, CS DBA wrote: Hi All; we've found a post about dumping blobs: / / /I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded

Re: [GENERAL] Read-only tables to avoid row visibility check

First of all, it would be really nice if you mentioned the version of PostgreSQL and O/S when posing questions. That being said, that wiki is a _discussion_, and as such, a suggestion on how it "might" be implemented. However, at this time, there is no such option as SET READ ONLY in any version

[GENERAL] ERROR: cannot convert relation containing dropped columns to view

Hello I get this error message : ERROR: cannot convert relation containing dropped columns to view I have googled, but find only very old posts that doesn't seem to be the same situation. What I have done is that I have a quite big table that I added a column to for deletion time. Then I

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

* Seamus Abshere (sea...@abshere.net) wrote: > Is there any other way to differentiate the 2 index scans? FWIW, 10% of > houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to > drop the index like Tom said.) Have to admit that I continue to be interested in this as it might

[GENERAL] Read-only tables to avoid row visibility check

hi, https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility of `ALTER TABLE table SET READ ONLY`. Would this mean that row visibility checks could be skipped and thus index-only scans much more common? Thanks, Seamus -- Seamus Abshere, SCEA +598 99 54 99 54

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

> On 22 Feb 2016, at 16:58, Tom Lane wrote: > > (BTW, is that index really on just a boolean column? It seems > unlikely that "phoneable" would be a sufficiently selective > condition to justify having an index on it. I'd seriously consider > dropping that index as another

Re: [GENERAL] decoding BLOB's

On 2/22/2016 11:23 AM, Joshua D. Drake wrote: Can someone point me in the right direction per how I would remove the first 25 bytes and the last 2 bytes from a bytea column? http://www.postgresql.org/docs/9.3/static/functions-binarystring.html Substring might do it for you. won't doing it

Re: [GENERAL] decoding BLOB's

Can someone point me in the right direction per how I would remove the first 25 bytes and the last 2 bytes from a bytea column? http://www.postgresql.org/docs/9.3/static/functions-binarystring.html Substring might do it for you. won't doing it in SQL still result in a BYTEA result which will

[GENERAL] foreign key to "some rows" of a second table

Hi All, So, I have a table that looks like this: CREATE TABLE config (     region    varchar(10),     name    varchar(10),     value    varchar(40) ); Another looks like this: CREATE TABLE tag (     host    varchar(10),    

Re: [GENERAL] foreign key to "some rows" of a second table

On 22/02/2016 13:03, Chris Withers wrote: Hi All, So, I have a table that looks like this: CREATE TABLE config ( regionvarchar(10), namevarchar(10), valuevarchar(40) ); Another looks like this: CREATE TABLE tag ( hostvarchar(10), typevarchar(10),

Re: [GENERAL] Why is my database so big?

Tom, thanks for your unbiased detailed response. Interesting post. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane Sent: 22 February 2016 05:06 To: Andrew Smith Cc: pgsql-general@postgresql.org Subject: Re:

[GENERAL] synch streaming replication question

Hi All I have a three node cluster using streaming replication configured as follows: One synch node and one asynch node. If my synch node is down the master node is hung until that node is brought back on line. Two questions: 1. Is this the expected action on my master? It makes sense

Re: [GENERAL] Get the date of creation of objects in the database

On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov wrote: > You may find useful a SIEM to record activity like this. > -- > From: David G. Johnston > Sent: ‎2/‎23/‎2016 0:14 > To: Edson F. Lidorio

Re: [GENERAL] Get the date of creation of objects in the database

On 02/22/2016 02:00 PM, Edson F. Lidorio wrote: Hello, How to get the date of creation of objects in batabase? For example: The date of creation of tables and trigger. AFAIK that is not recorded by default. If you are using Postgres 9.3+ you could roll your own audit system, going forward,

Re: [GENERAL] Read-only tables to avoid row visibility check

On Mon, Feb 22, 2016 at 2:38 PM, Seamus Abshere wrote: > On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote: > > However, at this time, there is no such option as SET READ ONLY in any > version of PostgreSQL. > > I know. > > I am wondering if hypothetical read-only

Re: [GENERAL] ERROR: cannot convert relation containing dropped columns to view

Tom Lane skrev > Nicklas =?ISO-8859-1?Q?Av=E9n?= writes: > > create table foo > > ( > > id serial, > > deleted int > > ); > > > alter table foo drop column deleted; > > alter table foo add column deleted timestamp; > > > CREATE or replace RULE del_post AS

Re: [GENERAL] Why is my database so big?

All, * FarjadFarid(ChkNet) (farjad.fa...@checknetworks.com) wrote: > Tom, thanks for your unbiased detailed response. > > Interesting post. Please don't top-post. My comments are in-line, below. > -Original Message- > From: pgsql-general-ow...@postgresql.org >

Re: [GENERAL] bpchar, text and indexes

2016-02-22 18:00 GMT+02:00 Thomas Kellerer : > I assume that this has to do with the fact that char(n) is blank padded to > 20 character. > Yes, this was my thought too, but I do not understand what is going on in details. To be able to correctly compare that to a text

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 01:48 PM, Tom Lane wrote: > Given how remarkably quick the single-index scan is, I also wonder if that > index is fully cached while we had to read some of the other index from > kernel or SSD. This makes sense, except that the speed of the query is the same if I run it

Re: [GENERAL] bpchar, text and indexes

Victor Yegorov writes: > Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast > for my constant. I do not understand why for `text` it cannot and casts > column instead. In cross-type comparisons like these, the parser basically has a choice between

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I've not looked into the specific costing here to see why the BitmapAnd > > ended up being chosen over just doing an index scan with the btree and > > then filtering, but I do believe it to be a problem area

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Stephen Frost writes: > I've not looked into the specific costing here to see why the BitmapAnd > ended up being chosen over just doing an index scan with the btree and > then filtering, but I do believe it to be a problem area that would be > good to try and improve. The

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > I don't understand why the query planner is choosing a BitmapAnd when an > Index Scan followed by a filter is obviously better. > On Postgres 9.4.4 with 244gb memory and SSDs > maintenance_work_mem 100 > work_mem 50 > random_page_cost 1 >

Re: [GENERAL] bpchar, text and indexes

Victor Yegorov schrieb am 22.02.2016 um 16:45: > Test setup: > > PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 > 20120313 (Red Hat 4.4.7-16), 64-bit > > create table t(t_id int4, sn_c char(20)); > insert into t select id, >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Seamus Abshere writes: > > I don't understand why the query planner is choosing a BitmapAnd when an > > Index Scan followed by a filter is obviously better. > > > On Postgres 9.4.4 with 244gb memory and SSDs > > >

[GENERAL] bpchar, text and indexes

Greetings. I'd like to understand why Postgres behaves the way it does. I was not able to find relevant mail thread myself, if one exists — please, point at it. Test setup: PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit create

Re: [GENERAL] Get the date of creation of objects in the database

On 22-02-2016 19:42, Melvin Davidson wrote: On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov > wrote: You may find useful a SIEM to record activity like this.

Re: [GENERAL] Get the date of creation of objects in the database

On 02/22/2016 03:24 PM, Edson F. Lidorio wrote: + 1 Listen11similar discussionat [1] [1] http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database Where the above leads to is implementing a version control system inside the

[GENERAL] Get the date of creation of objects in the database

Hello, How to get the date of creation of objects in batabase? For example: The date of creation of tables and trigger. Thank you; Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Read-only tables to avoid row visibility check

On Mon, Feb 22, 2016, at 06:48 PM, David G. Johnston wrote: > it would probably be more constructive to actually communicate the thoughts > that provoked the question. My company has a largish table - 250+ columns, 1 row for every household in the US. It's read-only. We've gotten advice to

Re: [GENERAL] Read-only tables to avoid row visibility check

On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote: > However, at this time, there is no such option as SET READ ONLY in any > version of PostgreSQL. I know. I am wondering if hypothetical read-only tables would make index-only scans more possible by avoiding the need for row visibility

Re: [GENERAL] Get the date of creation of objects in the database

On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio wrote: > Hello, > > How to get the date of creation of objects in batabase? > For example: The date of creation of tables and trigger. > ​The only source of data for that question is the local filesystem. If that is

Re: [GENERAL] ERROR: cannot convert relation containing dropped columns to view

Nicklas =?ISO-8859-1?Q?Av=E9n?= writes: > create table foo > ( > id serial, > deleted int > ); > alter table foo drop column deleted; > alter table foo add column deleted timestamp; > CREATE or replace RULE del_post AS ON DELETE TO foo > DO INSTEAD > update foo set

Re: [GENERAL] Get the date of creation of objects in the database

You may find useful a SIEM to record activity like this. -Original Message- From: "David G. Johnston" Sent: ‎2/‎23/‎2016 0:14 To: "Edson F. Lidorio" Cc: "pgsql-general" Subject: Re: [GENERAL] Get the date

Re: [GENERAL] Get the date of creation of objects in the database

Stephen Frost writes: > At least on a first blush look through the threads linked from such a > search, I'm unimpressed by the arguments against and note that there are > quite a few arguments for. I think you missed the worries around what dump/reload semantics would be. >

Re: [GENERAL] Get the date of creation of objects in the database

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > At least on a first blush look through the threads linked from such a > > search, I'm unimpressed by the arguments against and note that there are > > quite a few arguments for. > > I think you missed the

Re: [GENERAL] synch streaming replication question

On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek wrote: > I have a three node cluster using streaming replication configured as > follows: > One synch node and one asynch node. > If my synch node is down the master node is hung until that node is brought > back on line. > >

Re: [GENERAL] synch streaming replication question

On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier wrote: > On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek wrote: > >> I have a three node cluster using streaming replication configured as >> follows: >> One synch node and one asynch node. >> If my

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Given how remarkably quick the single-index scan is, I also wonder if >> that index is fully cached while we had to read some of the other index >> from kernel or SSD. > Unfortunately, this doesn't actually

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote: > IOW, almost certainly we *don't* realize that the query will involve scanning > through gigabytes of index pages. But btree indexes are much simpler and > easier to make that estimate for... Isn't this the crux of my issue, at least? --

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > "fixed" my problem by preventing the BitmapAnd. > Is this dangerous? Use a gentle tap, man, don't swing the hammer with quite so much abandon. I'd have tried doubling the setting

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:30 PM, Jeff Janes wrote: > It charges 0.1 CPU_operator_cost, while reality seemed to be more like 6 > CPU_operator_cost. fdy=> select name, setting, boot_val from pg_settings where name ~ 'cpu'; name | setting | boot_val

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote: > Seamus Abshere writes: > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > > "fixed" my problem by preventing the BitmapAnd. > > Is this dangerous? > > Use a gentle tap, man, don't swing the hammer

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote: >> IOW, almost certainly we *don't* realize that the query will involve >> scanning through gigabytes of index pages. But btree indexes are much >> simpler and easier to make that estimate for... >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Seamus Abshere writes: > Is there any other way to differentiate the 2 index scans? FWIW, 10% of > houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to > drop the index like Tom said.) Hm. 10% is above the threshold where I'd usually think that an

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016 at 8:20 AM, Stephen Frost wrote: > > Also agreed here, but I've seen field evidence (with reasonable > configurations) that definitely shows that we're a bit too happy to go > with a BitmapAnd scan across two indexes where one returns an order of >

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

Jeff Janes writes: > I looked into this before as well, and I think it is vastly > underestimating the cost of adding a bit into the bitmap, near this > comment: > /* > * Charge a small amount per retrieved tuple to reflect the costs of > *

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

On Mon, Feb 22, 2016, at 02:53 PM, Seamus Abshere wrote: > On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote: > > Seamus Abshere writes: > > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It > > > "fixed" my problem by preventing the BitmapAnd. > > > Is

Re: [GENERAL] synch streaming replication question

How do I configure both standbys as ³synch² nodes? Do I list both nodenames or ip addresesses in the postgresql.conf synchronous_standyby_names entry? My current entery looks like this: synchronous_standby_names = 'keypg2,*' John On 2/22/16, 8:25 PM, "Thomas Munro"

[GENERAL] repmgr faiover question

Hello I have a three node postgresql 9.4 cluster configured with one standby in sync mode and the other in asynch mode. If the master datbase is shutdown the pg promote happens as expected. However on the remaining standby node the RECOVERY.CONF file is misconfigured when issuing the command:

Re: [GENERAL] synch streaming replication question

On Tue, Feb 23, 2016 at 12:02 PM, John Wiencek wrote: > How do I configure both standbys as ³synch² nodes? > > Do I list both nodenames or ip addresesses in the postgresql.conf > synchronous_standyby_names entry? > > My current entery looks like this: > >

Re: [GENERAL] Live steraming replication setup issue!

On Tue, Feb 23, 2016 at 10:02 AM, Ashish Chauhan wrote: > Thanks Venkata, I am able to setup replication now. Just wondering when I > check replication_delay and lag, I am getting negative number, any idea why? > > > > receive|replay| replication_delay |

Re: [GENERAL] Get the date of creation of objects in the database

David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > The only source of data for that question is the local filesystem. If > that is acceptable you can find examples online provided to others who have > asked this question. What on the local filesystem would help here..? All you

Re: [GENERAL] Read-only tables to avoid row visibility check

On Mon, Feb 22, 2016 at 2:35 PM, Seamus Abshere wrote: > hi, > > https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility > of `ALTER TABLE table SET READ ONLY`. > > Would this mean that row visibility checks could be skipped and thus > index-only scans much

Re: [GENERAL] Read-only tables to avoid row visibility check

Seamus Abshere writes: > Being able to tell postgres that our table is "Read Only" has imaginary > mystical properties for me, first and foremost being able to count > against indexes without ever hitting the disk. >> ​If the system is working properly then a READ ONLY

[GENERAL] Why does query planner choose slower BitmapAnd ?

hi, I don't understand why the query planner is choosing a BitmapAnd when an Index Scan followed by a filter is obviously better. (Note that "new_york_houses" is a view of table "houses" with one condition on city - and there is an index idx_houses_city. That is the Index Scan that I think it