Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

On 11/16/2015 10:55 PM, zh1029 wrote: Unfortunately what's the process 2720 is unknown as no any else log to indicate it. It might because the debug level of PostgreSQL had been set too lower to show more. if its a linux system, try... ps uww -p 2720 logged on as root, and it should show

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

Hi, Per my understanding, From the query which resulted in the deadlock “SELECT sequence_name, start_value, increment_by***”, it appears that the query which holds the lock is related to sequence numbers. From our understanding it appears that, whenever there is a serial type of data mem

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

Unfortunately what's the process 2720 is unknown as no any else log to indicate it. It might because the debug level of PostgreSQL had been set too lower to show more. -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

Hi, pg_restore is executed by our own process actually, before that, our process connects the data base via ODBC to access database template1 see if PostgreSQL server is up. But pg_restore still failed after connect database I understand the result is same as pg_ctrl output, is that true? Thank

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

On 11/16/2015 08:24 PM, zh1029 wrote: Hi, While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL bringing up. I encounter pg_restore failure because of deadlock detected. postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for AccessExclusiveLock on rela

Re: [GENERAL] Importing directly from BCP files

On 11/16/2015 05:47 PM, Tim Uckun wrote: I am going to ask them that but there are tons of legacy files which might need to be dealt with again in the future so I was hoping to use them directly. Seems it comes down to who is going to have to do the dealing, you or the folks supplying the file

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

On Tue, Nov 17, 2015 at 3:24 PM, zh1029 wrote: > Hi, > > While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL > bringing up. I encounter pg_restore failure because of deadlock detected. > > postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting > for > Acce

Re: [GENERAL] postgreSQL.conf has become zero byte file

On 11/16/2015 06:59 PM, M Tarkeshwar Rao wrote: Hi All, In our production setup we found new issue as postgreSQL.conf has become zero byte file. After some time we copied that file from some back up, after some time it has again become zero byte. Any clue what is the reason of this behavior.

[GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

Hi, While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL bringing up. I encounter pg_restore failure because of deadlock detected. postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for AccessExclusiveLock on relation 7 of database 24577 after 1000.

[GENERAL] postgreSQL.conf has become zero byte file

Hi All, In our production setup we found new issue as postgreSQL.conf has become zero byte file. After some time we copied that file from some back up, after some time it has again become zero byte. Any clue what is the reason of this behavior. Regards Tarkeshwar

Re: [GENERAL] bdr appears to be trying to replicate to itself

> On 17 November 2015 at 00:33, Cj B > wrote: > > This doesn't explain how the system got into this state. For that it'd really > be necessary to see the steps taken during setup. BDR tries to protect > against attempts to replicate-from-self. Presumably there's an

Re: [GENERAL] Importing directly from BCP files

I am going to ask them that but there are tons of legacy files which might need to be dealt with again in the future so I was hoping to use them directly.

Re: [GENERAL] bdr appears to be trying to replicate to itself

On 17 November 2015 at 00:33, Cj B wrote: > select pg_drop_replication_slot(‘bdr_16385_6188730679935789649_1_16385__’) > Correct. > What impact will this have? If the slot is unused, it'll allow the WAL that's being held by the slot to be removed. It'll also unpin the catalog xmin to allow

Re: [GENERAL] Importing directly from BCP files

On 11/16/2015 04:25 PM, Tim Uckun wrote: the binary form of the BCP file output is undocumented. So if all the BCP files you have are the binary(native) version you are up this creek without a paddle. Ugh. Yes it looks like that's the creek I am on. Thanks Microsoft! So, mov

Re: [GENERAL] Adding a new module to postgres

On Tue, Nov 17, 2015 at 1:09 AM, Mohammed Ajil wrote: > I am currently working on a research project for Secure Access Control > in PostgreSQL. So basically you wish to decide which query is authorized to run or not depending on its type as well as on the relation a given query touches. I would i

Re: [GENERAL] Importing directly from BCP files

> > the binary form of the BCP file output is undocumented. So if all the BCP > files you have are the binary(native) version you are up this creek without > a paddle. > > Ugh. Yes it looks like that's the creek I am on. Thanks Microsoft! > So, moving to another creek. It depends on the amount

Re: [GENERAL] Importing directly from BCP files

On 11/16/2015 12:15 PM, Tim Uckun wrote: On openSuSE 13.2 sudo zypper install freetds-tools Ubuntu 14.04 sudo apt-get install freetds-bin aklaver@killi:~> freebcp -h usage: freebcp [[database_name.]owner.]table_name {in | out} datafile [-m maxerrors] [-f

Re: [GENERAL] update inside function does not use the index

That solves my problem. Thanks!! Best regards Johannes Am 16.11.2015 um 18:19 schrieb Tom Lane: > Adrian Klaver writes: >> On 11/16/2015 08:03 AM, Johannes wrote: In every loop I execute an update with a where LIKE condition, which relates to my current cursor position: FOR i IN S

Re: [GENERAL] Importing directly from BCP files

On Monday, November 16, 2015 1:15 PM, Tim Uckun wrote: > I don't see any documentation anywhere about the BCP format and > as I said googling for "BCP format" gives a zillion links about > the format files. That's because there is no one, single "BCP format" -- the format of a BCP file is determ

Re: [GENERAL] update inside function does not use the index

A function seams to be atomic for the analyze command (or?) EXPLAIN ANALYZE select my_function(); returns no inner query plan, just the costs, rows and width Am 16.11.2015 um 17:57 schrieb Adrian Klaver: > EXPLAIN ANALYZE select ... your_function(...); signature.asc Description: OpenPGP d

Re: [GENERAL] update inside function does not use the index

No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; Sorry. Best regards Johannes Am 16.11.2015 um 15:10 schrieb Adrian Klaver: > On 11/16/2015 05:56

Re: [GENERAL] update inside function does not use the index

This helps me to understand for these common table expressions better. Thanks. This looks more elegant than the cursor variant. Limiting the cte to 10 records the update query needs 1.8 seconds. But the cursor variant ( 10 records ) was finished in 0.7 seconds. I guess it is faster, because behind

Re: [GENERAL] Importing directly from BCP files

> > > On openSuSE 13.2 > > sudo zypper install freetds-tools > > Ubuntu 14.04 > > sudo apt-get install freetds-bin > > aklaver@killi:~> freebcp -h > usage: freebcp [[database_name.]owner.]table_name {in | out} datafile > [-m maxerrors] [-f formatfile] [-e errfile] > [-F firstrow] [

Re: [GENERAL] Importing directly from BCP files

Tim Uckun wrote: > > The bulk import command in PostgreSQL is COPY, so you'll likely want to > > look at http://www.postgresql.org/docs/9.4/static/sql-copy.html > > I need to get them into a shape where copy command can process them first I > think. Maybe transform the format file into something

Re: [GENERAL] Importing directly from BCP files

On 11/16/2015 11:13 AM, Tim Uckun wrote: I don't see any documentation anywhere about the BCP format and as I said googling for "BCP format" gives a zillion links about the format files. Every project on github just calls out the BCP command but I am on linux and that doesn't help me at all. B

Re: [GENERAL] Importing directly from BCP files

I don't see any documentation anywhere about the BCP format and as I said googling for "BCP format" gives a zillion links about the format files. Every project on github just calls out the BCP command but I am on linux and that doesn't help me at all. Bummer. This is going to be a huge pain to tr

[GENERAL] Adding a new module to postgres

Dear Postgres Team, I am currently working on a research project for Secure Access Control in PostgreSQL. For verifying my hypothesis I would like to include my own algorithm that makes the access control decisions for specific queries. For that I would like to include my own access-control.c file

Re: [GENERAL] Importing directly from BCP files

On 11/15/2015 11:52 PM, Tim Uckun wrote: > > I moved a database from MS Sql Server 2000 to Postgresql a few years > > ago via BCP files. I used a Python script to do some fixup on the > > BCP files to make them importable as CSV files into Postgresql. I > > don't know if quirks I ran into are

Re: [GENERAL] update inside function does not use the index

Adrian Klaver writes: > On 11/16/2015 08:03 AM, Johannes wrote: >>> In every loop I execute an update with a where LIKE condition, which >>> relates to my current cursor position: >>> FOR i IN SELECT id, level_ids, path_names||'%' as path_names from x LOOP >>> update x set path_ids[i.level] = id w

Re: [GENERAL] update inside function does not use the index

On 11/16/2015 08:03 AM, Johannes wrote: Ccing list No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; So what do you see if you do?: EXPLAIN AN

Re: [GENERAL] bdr appears to be trying to replicate to itself

Hi, Yes, I posted on github because I wasn’t sure where to post. And the reason I’m posting here is because I’m not clear about the answer "Drop the slot bdr_16385_6188730679935789649_1_16385__ on the first host.” Do this just mean to select pg_drop_replication_slot(‘bdr_16385_6188730679935789

Re: [GENERAL] bdr appears to be trying to replicate to itself

Cj B wrote: > I noticed a very strange issue starting about 20 days ago and my pg_xlog has > just been filling up since then. For reference, this was also asked on github, and answered there. See https://github.com/2ndQuadrant/bdr/issues/143 -- Álvaro Herrerahttp://www.2ndQuadr

Re: [GENERAL] referencing other INSERT VALUES columns inside the insert

On Mon, Nov 16, 2015 at 4:06 AM, Geoff Winkless wrote: > On 16 November 2015 at 10:55, Albe Laurenz > wrote: > >> What about something along these lines: >> >> INSERT INTO test (c1, c2, c3) >>(WITH fixed(x1, x2) AS (VALUES (3, 7)) >> SELECT x1, x2, x1 * x2 FROM fixed); >> > > ​Genius! >

[GENERAL] bdr appears to be trying to replicate to itself

I noticed a very strange issue starting about 20 days ago and my pg_xlog has just been filling up since then. ``` HOST A: postgres=# select * from pg_replication_slots; slot_name| plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_ls

Re: [GENERAL] database corrupt

Willy-Bas Loos writes: > However, our test database cluster seems to be broken. > When i try to start the cluster it says: > 2015-11-16 15:06:35 CET db: ip: us: LOG: database system was interrupted > while in recovery at 2015-11-16 13:05:41 CET > 2015-11-16 15:06:35 CET db: ip: us: HINT: This pr

[GENERAL] database corrupt

Hi, We've had a harware failure at the data center, the provider said that "the chip" burned. Most systems came back online after a new mainbord was installed. However, our test database cluster seems to be broken. When i try to start the cluster it says: 2015-11-16 15:06:35 CET db: ip: us: LOG:

Re: [GENERAL] update inside function does not use the index

Johannes schrieb am 16.11.2015 um 14:56: > I have problems with a self written function, which does not use the > index, which takes very long (500 ms per update). > > The pl/pgsql function iterates over a select resultset with a cursor. > In every loop I execute an update with a where LIKE condit

Re: [GENERAL] update inside function does not use the index

On 11/16/2015 05:56 AM, Johannes wrote: Dear List, I have problems with a self written function, which does not use the index, which takes very long (500 ms per update). The pl/pgsql function iterates over a select resultset with a cursor. In every loop I execute an update with a where LIKE co

[GENERAL] update inside function does not use the index

Dear List, I have problems with a self written function, which does not use the index, which takes very long (500 ms per update). The pl/pgsql function iterates over a select resultset with a cursor. In every loop I execute an update with a where LIKE condition, which relates to my current curso

Re: [GENERAL] can postgres run well on NFS mounted partitions?

Another good idea is to do a grep FATAL: your_postgres_log and see if PostgreSQL is complaining about anything relating to table. index or WAL access, otherwise, usually the only "acceptable" FATAL's are related to pg_hba.conf authorization or other connection problems. On Mon, Nov 16, 2015 at 3

Re: [GENERAL] DB_link connection

On 11/16/2015 3:39 AM, Sachin Srivastava wrote: please reply to the list so that others in similar situation can benefit from the eventual solution. Please find the output of rpm -qa |grep postgres -bash-4.1# rpm -qa |grep postgres postgresql-8.4.9-1.el6_1.1.x86_64 postgresql-libs-8.4.9-1.e

Re: [GENERAL] DB_link connection

On 11/16/2015 2:55 AM, Sachin Srivastava wrote: But while installing the package " # yum install postgresql94-contrib"getting below error. -bash-4.1# yum install postgresql-9.4-contrib what output do you get from... # rpm -qa |grep postgres ? I didn't see the yum.postgresql.org repo l

Re: [GENERAL] referencing other INSERT VALUES columns inside the insert

On 16 November 2015 at 10:55, Albe Laurenz wrote: > What about something along these lines: > > INSERT INTO test (c1, c2, c3) >(WITH fixed(x1, x2) AS (VALUES (3, 7)) > SELECT x1, x2, x1 * x2 FROM fixed); > ​Genius! It never occured to me that the with_query parameter could be used that

Re: [GENERAL] referencing other INSERT VALUES columns inside the insert

Geoff Winkless wrote: > I know that this is something that can't be done... > > CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0); > INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2); > > Is there a known trick to work around it (so that the values inserted into c1 > an

Re: [GENERAL] DB_link connection

On 11/16/2015 2:31 AM, Sachin Srivastava wrote: Any one can help me how we can install "dblink_connect" in PostgreSQL9.4 on linux platform. If Possible, please provide the steps. that is part of the 'contributed extension' dblink.the way you install the contrib modules depends on your f

[GENERAL] DB_link connection

Hi, Any one can help me how we can install "dblink_connect" in PostgreSQL9.4 on linux platform. If Possible, please provide the steps. Regards, Sachin Srivastava 9811149139

[GENERAL] referencing other INSERT VALUES columns inside the insert

I know that this is something that can't be done... CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0); INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2); Is there a known trick to work around it (so that the values inserted into c1 and c2 is referenced back to c3), other

Re: [GENERAL] can postgres run well on NFS mounted partitions?

anj patnaik wrote: > How do you tell if a database is corrupted? Are there specific error > messages/symptoms to look for? That's actually a pretty tough question. The standard test is to run "pg_dumpall", see if it finishes without error and if the dump can be restored without error. That won't