[GENERAL] Is "WITH () UPDATE" Thread Safe ?

Hello, I have a lot of tickets, i need to take a batch of tickets and process them. So the process is : SELECT ONLY 100 tickets PROCESS ticket MARK THEM AS « done » I’m selecting the tickets with : WITH t0 AS (     SELECT t.id,  RANDOM() AS rank, EXTRACT(EPOCH FR

[GENERAL] What is default password for user postgres

Hi all, I installed version 9.1 in my Ubuntu OS, but not able to login. What is default password for user Postgres? Regards Tarkeshwar

Re: [GENERAL] Is "WITH () UPDATE" Thread Safe ?

Paul GOERGLER wrote: > I have a lot of tickets, i need to take a batch of tickets and process them. > So the process is : > SELECT ONLY 100 tickets > PROCESS ticket > MARK THEM AS « done » > > I’m selecting the tickets with : > > WITH t0 AS ( > SELECT t.id, > RANDOM() AS rank, > EXTRACT(EPOCH

Re: [GENERAL] What is default password for user postgres

On 1 December 2014 at 09:08, M Tarkeshwar Rao wrote: > Hi all, > > > > I installed version 9.1 in my Ubuntu OS, but not able to login. > > What is default password for user Postgres? > The postgres user doesn't have a password by default, which is probably how you should keep it. Typically the

Re: [GENERAL] Ever increasing pg_clog disk usage v8.4

On Mon, 1 Dec 2014 01:34:14 +0100 "Powrie, William" wrote: > Hello, > > I have a simple database with just a few tables that runs on an > embedded Linux system 2.6.31.8. The OS including postresql 8.4 is > loaded directly from cf-flash media and is not saved in any way > across power recycles. It

Re: [GENERAL] Synchronous Replication Timeout

> From: Teresa Bradbury >To: "pgsql-general@postgresql.org" >Sent: Friday, 28 November 2014, 2:24 >Subject: [GENERAL] Synchronous Replication Timeout > > >Hi, > >I have a replication setup with a master and a single synchronous slave. If >the slave dies (or the network goes down) I would lik

Re: [GENERAL] What is default password for user postgres

On 12/01/2014 02:30 AM, Thom Brown wrote: On 1 December 2014 at 09:08, M Tarkeshwar Rao mailto:m.tarkeshwar@ericsson.com>> wrote: Hi all, __ __ I installed version 9.1 in my Ubuntu OS, but not able to login. What is default password for user Postgres? The postgre

Re: [GENERAL] Lock Management: Waiting on locks

On Wed, Nov 26, 2014 at 1:57 PM, Dev Kumkar wrote: > Can you let me know what exact details should be provided here? > You could start with the version of Postgres you're using.

Re: [GENERAL] Best filesystem for a high load db

On Wed, Nov 26, 2014 at 10:53 AM, Joseph Kregloh wrote: > One thing I am pushing to do is using SSDs for the ZIL and L2ARC. This > would allow for a pretty nice boost in speed. > I use ZFS on freebsd as my large production server configuration as well. I have SSDs for the L2ARC and that works *r

Re: [GENERAL] Re: Remote PostgreSQL database - C/C++ program / Unix / Required Libraries

On Sun, 2014-11-30 at 12:51 -0700, Léa Massiot wrote: > Me again. Sorry. > > This looks good too: "Building libpq Programs": > http://www.postgresql.org/docs/9.1/static/libpq-build.html > > But what do I need to install on B? > > Best regards. > > > > -- > View this message in context: > htt

Re: [GENERAL] Ever increasing pg_clog disk usage v8.4

Eduardo Morras writes: > "Powrie, William" wrote: >> The files in pg_clog increase on a day to day basis until the ramdisk >> reaches 100% utilization. This takes roughly 30 days to occur and >> postgresql fails at this point. > pg_clog stores information about current commit status for open/act

Re: [GENERAL] Ever increasing pg_clog disk usage v8.4

On 11/30/2014 04:34 PM, Powrie, William wrote: Hello, I have a simple database with just a few tables that runs on an embedded Linux system 2.6.31.8. The OS including postresql 8.4 is loaded directly from cf-flash media and is not saved in any way across power recycles. It is always created at s

Re: [GENERAL] recovering from "too many failures" wal error

On 11/29/2014 3:37 PM, CS DBA wrote: All; We have a postgresql 9.2 cluster setup to do continuous wal archiving. We were archiving to a mount point that went offline. As a result the db could not archive the wal files, we ended up with many many errors in the logs indicating the file could not b

Re: [GENERAL] PG94RC1- plv8 functions - problem with input parameter length

On Friday, November 28, 2014, Adrian Klaver wrote: > On 11/28/2014 12:18 PM, Misa Simic wrote: > >> >> >> On Friday, November 28, 2014, Adrian Klaver > > wrote: >> >> On 11/28/2014 07:56 AM, Misa Simic wrote: >> >> Hi all, >> >> We have found

[GENERAL] Slow delete when many foreign tables are defined

Hello, I have a main table and a lot of "details" tables that reference the main one. Every time I delete a record from the main table, a check is done on every details table that contain a foreign key toward main table. This is a simplified schema: create table main ( type varchar, serial numer

Re: [GENERAL] PG94RC1- plv8 functions - problem with input parameter length

On 12/01/2014 07:20 AM, Misa Simic wrote: Thanks Adrian, Not sure any more where problem could be Best guess, interaction of plV8 with system libV8 libraries and some change in 9.4rc1. Not very specific I know. plv8 is not part of the core software for Postgres, so to make progress

[GENERAL] The file of toc.dat (got by using the command: pg_dump -Fd ) may be need to have readability

Hi all, When using the command "pg_dump -Fd" to backup data, I find the content of toc.dat is hard for read, just like this: " ^@"^@^@^@DROP SEQUENCE public.test_id_seq" , So may be we should do some work to make the file "toc.dat" more readable. Thanks, Lin

Re: [GENERAL] PG94RC1- plv8 functions - problem with input parameter length

On 12/01/2014 07:20 AM, Misa Simic wrote: -- Adrian Klaver adrian.kla...@aklaver.com Thanks Adrian, Not sure any more where problem could be We have just found that it creates problems on machines with ubuntu 14.10 (pg94rc1) - we have tried to install 14.10 in different e

Re: [GENERAL] The file of toc.dat (got by using the command: pg_dump -Fd ) may be need to have readability

On 12/01/2014 07:35 AM, lin wrote: Hi all, When using the command "pg_dump -Fd" to backup data, I find the content of toc.dat is hard for read, just like this: " ^@"^@^@^@DROP SEQUENCE public.test_id_seq" , So may be we should do some work to make the file "toc.dat" more readable. I believe p

Re: [GENERAL] Slow delete when many foreign tables are defined

On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: Hello, I have a main table and a lot of "details" tables that reference the main one. Every time I delete a record from the main table, a check is done on every details table that contain a foreign key toward main table. This is a simplified schema:

Re: [GENERAL] Slow delete when many foreign tables are defined

Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto: > On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: > > Hello, > > I have a main table and a lot of "details" tables that reference the > > main one. > > > > Every time I delete a record from the main table, a check is done on > > every

Re: [GENERAL] Slow delete when many foreign tables are defined

On 1 December 2014 at 17:21, Giuseppe Sacco wrote: > Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto: >> On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: >> 2) Try inheritance. I have no idea if it'll help, but I thought I'd >> read someplace where the planner knew a little more ab

[GENERAL] Programmatic access to interval units

Good morning list, According to the documentation for interval data type inputs, the unit can be one of microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, or millennium. Are these units stored in a catalog somewhere? I would like to access them programmaticall

Re: [GENERAL] Slow delete when many foreign tables are defined

On 12/1/2014 10:21 AM, Giuseppe Sacco wrote: Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto: On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: Hello, I have a main table and a lot of "details" tables that reference the main one. Every time I delete a record from the main table, a

Re: [GENERAL] Slow delete when many foreign tables are defined

On 12/1/2014 10:37 AM, Alban Hertroys wrote: On 1 December 2014 at 17:21, Giuseppe Sacco wrote: Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto: On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: 2) Try inheritance. I have no idea if it'll help, but I thought I'd read someplace

Re: [GENERAL] Slow delete when many foreign tables are defined

On Mon, 01 Dec 2014 11:00:51 -0600 Andy Colson wrote: > On 12/1/2014 10:21 AM, Giuseppe Sacco wrote: > > Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto: > >> On 12/1/2014 9:23 AM, Giuseppe Sacco wrote: > >>> Hello, > >>> I have a main table and a lot of "details" tables that r

Re: [GENERAL] recovering from "too many failures" wal error

On 2014-11-29 14:37:56 -0700, CS DBA wrote: > All; > > We have a postgresql 9.2 cluster setup to do continuous wal archiving. We > were archiving to a mount point that went offline. As a result the db could > not archive the wal files, we ended up with many many errors in the logs > indicating the

Re: [GENERAL] PG94RC1- plv8 functions - problem with input parameter length

On 2014-12-01 16:20:54 +0100, Misa Simic wrote: > We have just found that it creates problems on machines with ubuntu 14.10 > (pg94rc1) - we have tried to install 14.10 in different environments > (cloud, xen, virtual box) - and everywhere problems happens... I suggest running postgres under valgr

Re: [GENERAL] Programmatic access to interval units

On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green wrote: > Good morning list, > > According to the documentation for interval data type inputs, the unit can > be one of microsecond, millisecond, second, minute, hour, day, week, month, > year, decade, century, or millennium. Are these units stored in a

[GENERAL] Irreversible SET ROLE

Hi, I have a question about preventing SET ROLE from being reset within a session. I'll give some context for my question, but please note that the question is not restricted to the technologies (XWiki, Groovy) that I'm using. I'm working with a PostgreSQL 9.3 database that is interfaced via JD

Re: [GENERAL] Irreversible SET ROLE

>The problem for me is that SET ROLE can be reversed with SET ROLE >NONE or RESET ROLE, so a user could set the role to access rows that >they should not be able to see. *This is only partially true. While they can do SET ROLE NONE & RESET ROLE, they Cannot SET ROLE to a role they have not been g

[GENERAL] Merge rows based on Levenshtein distance

I am new to PostgreSQL and I have the following table: Name, City "Alex", "Washington" "Aleex1", "Washington" "Bob", "NYC" "Booob", "NYC" I want to "merge" similar rows based on levenshtein distance between names so that I have the following table: id, Name, City 1,"Alex", "Washington" 1,"Aleex1

Re: [GENERAL] Irreversible SET ROLE

Bryn Jeffries writes: > I have a question about preventing SET ROLE from being reset within a session. You can't; per SQL standard, SET ROLE NONE is supposed to do exactly that. I think you might be able to do something with invoking untrusted code inside a SECURITY DEFINER function. That conte

Re: [GENERAL] Merge rows based on Levenshtein distance

mongoose wrote > I am new to PostgreSQL and I have the following table: > > Name, City > "Alex", "Washington" > "Aleex1", "Washington" > "Bob", "NYC" > "Booob", "NYC" > > I want to "merge" similar rows based on levenshtein distance between names > so that I have the following table: > > id, Name

Re: [GENERAL] Irreversible SET ROLE

Tom Lane wrote >> I have a question about preventing SET ROLE from being reset within a >> session. > > You can't; per SQL standard, SET ROLE NONE is supposed to do exactly that. > > I think you might be able to do something with invoking untrusted code > inside a SECURITY DEFINER function. That

Re: [GENERAL] Irreversible SET ROLE

Melvin Davidson wrote: >> The problem for me is that SET ROLE can be reversed with SET ROLE >> NONE or RESET ROLE, so a user could set the role to access rows that >> they should not be able to see. > > This is only partially true. While they can do SET ROLE NONE & RESET ROLE, > they Cannot SET ROL

Re: [GENERAL] Irreversible SET ROLE

Bryn Jeffries writes: > Tom Lane wrote >> I think you might be able to do something with invoking untrusted code >> inside a SECURITY DEFINER function. That context disables both SET ROLE >> and SET SESSION AUTHORIZATION. I would not want to bet a lot on that >> being bulletproof, however. > Me

Re: [GENERAL] PG94RC1- plv8 functions - problem with input parameter length

On Fri, 28 Nov 2014 16:56:01 +0100 Misa Simic wrote: > Hi all, Hello, on Debian Sid and all works fine. postgresql-9.4: Installed: 9.4~rc1-1.pgdg+1 from http://apt.postgresql.org/ plv8js from https://code.google.com/p/plv8js/ > > We have found a strange problem with plv8 functions in PG94RC1