[GENERAL] Update field to a column from another table

I've got two tables: - ja_jobs - junk.ja_jobs_23856 I need to update the null column ja_jobs.time_job with the data from the table junk.ja_jobs_23856 So I'm doing: > > UPDATE public.ja_jobs AS b > SET time_job = a.time_job > FROM junk.ja_jobs_23856 AS a > WHERE a.id = b.id > AND a.clientid

Re: [GENERAL] Is it possible to call Postgres directly?

On 4/20/2016 7:27 PM, da...@andl.org wrote: Re (1): At present I can do it like this: SELECT * FROM COMPILE($$ $$); But I would like to avoid the outer SQL SQL wrapper. so all your top level application queries are of the form SELECT * from FUNCTION() ? thats not going to do a very good

Re: [GENERAL] Is it possible to call Postgres directly?

writes: > I would like to be able to execute SQL queries by a direct call into > Postgres, without going through either (1) the language call handler or (2) > a 'wire' protocol. What would you consider to be "directly"? What advantage would you hope to gain from a different

[GENERAL] Is it possible to call Postgres directly?

I am attempting to create a new language implementation. The language is Andl (see http://www.andl.org/2016/04/postgres-meet-andl/). I would like to be able to execute SQL queries by a direct call into Postgres, without going through either (1) the language call handler or (2) a 'wire' protocol.

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

Karl Czajkowski writes: > On Apr 20, Tom Lane modulated: >> Just out of curiosity, why are you doing it like that, and not simply >> USING ('example attribute value' = ANY (current_attributes())) >> It seems like you're going out of your way to complicate matters. > Going out of

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Apr 20, Tom Lane modulated: > Karl Czajkowski writes: > > CREATE POLICY delete_stuff ON stuff > > FOR DELETE USING ('example attribute value' = ANY ( ((SELECT > > current_attributes()))::text[] )); > > Just out of curiosity, why are you doing it like that, and not simply

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

> >> Define "executes". You could shove those lines in via the wire > >> protocol, sure, but SPI won't take them. > > > Now you really have me puzzled. What I provided is an extract from the > > log of generated SQL commands sent to the SPI interface. [Obviously > > there also values bound to

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 6:04 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Apr 20, 2016 at 5:18 PM, Karl Czajkowski wrote: > >> >> CREATE POLICY delete_stuff ON stuff >> FOR DELETE USING ('example attribute value' = ANY ( ((SELECT >>

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 5:18 PM, Karl Czajkowski wrote: > > CREATE POLICY delete_stuff ON stuff > FOR DELETE USING ('example attribute value' = ANY ( ((SELECT > current_attributes()))::text[] )); > > The following (untested) structure should be immune to this problem...use

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 5:35 PM, Adrian Klaver wrote: > On 04/20/2016 05:18 PM, Karl Czajkowski wrote: > >> Our row level policies require very explicit casts in order to be >> accepted by the DB, but those casts are discarded in the dumped policy >> statements. Thus,

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 5:30 PM, Melvin Davidson wrote: > On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver > wrote: > "I just cannot see that in the following: > pg_upgrade 9.4 --> 9.5" > The 9.5 database is the same as the 9.4 one." > > You are

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver wrote: > On 04/20/2016 04:33 PM, Melvin Davidson wrote: > >> >> >> > >> >> "Not until pg_upgrade is done or replication is started, in either case >> a new cluster is started probably at a different time from the original

[GENERAL] RLS policy dump/restore failure due to elided type-casts

Our row level policies require very explicit casts in order to be accepted by the DB, but those casts are discarded in the dumped policy statements. Thus, an attempt to reload the dump file fails. I'm not sure if the underlying problem is that the cast shouldn't be required in the first place, or

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On 04/20/2016 04:33 PM, Melvin Davidson wrote: "Not until pg_upgrade is done or replication is started, in either case a new cluster is started probably at a different time from the original cluster." Not true, whether an upgrade or rep[lication, the relcreatedat time will not/cannot

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 7:22 PM, Adrian Klaver wrote: > On 04/20/2016 04:09 PM, Melvin Davidson wrote: > >> >> On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner > > wrote: >> >> On Wed, Apr 20, 2016 at 4:40 PM, Melvin

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On 04/20/2016 04:09 PM, Melvin Davidson wrote: On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner > wrote: On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson > wrote: > As for what I want

Re: [GENERAL] Function PostgreSQL 9.2

On 04/20/2016 03:33 PM, drum.lu...@gmail.com wrote: If I am following, this duplicates the information in companies.client_code_increment, in that they both return the last non-user code. Of course this assumes, as David mentioned, that the client is not using a numeric code

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 3:33 PM, drum.lu...@gmail.com wrote: The customer can add any value into users.code: code CHARACTER VARYING, what if he puts in a non-unique value ? But he also can let it blank/null if he wants to. That's when the trigger do its job.. Put a value (starting in 1000) in

Re: [GENERAL] Function PostgreSQL 9.2

> > > > If I am following, this duplicates the information in > companies.client_code_increment, in that they both return the last non-user > code. Of course this assumes, as David mentioned, that the client is not > using a numeric code system. Then you are left trying to figure whether a >

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson wrote: > As for what I want from the community, I would like other users > and dba's to weigh in on this request and it's usefulness. When I was a DBA on a team responsible for hundreds of geographically distributed

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 2:51 PM, drum.lu...@gmail.com wrote: > 1 - The customer can add any value into users.code column > 2 - The customer can chose between *add or no**t* add the value on > users.code column > 3 - If users.code is null (because the customer's chosen not

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 2:51 PM, drum.lu...@gmail.com wrote: 1 - The customer can add any value into users.code column 2 - The customer can chose between *add or no**t* add the value on users.code column 3 - If users.code is null (because the customer's chosen not to add any value in there), a

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On 04/20/2016 02:40 PM, Melvin Davidson wrote: On Wed, Apr 20, 2016 at 5:14 PM, David G. Johnston Really, it's that hard to add another column to an existing system catalog and document it? Hmm, let's try ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreatedat timestamp default now();

Re: [GENERAL] Function PostgreSQL 9.2

On 21 April 2016 at 09:44, Adrian Klaver wrote: > On 04/19/2016 07:34 PM, drum.lu...@gmail.com wrote: > >> Information. eg.: >> >> >> The schema for the tables. >> >> Why is not just adding a DEFAULT value to the users.code not an >> option? >> >> >> >> The

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 07:34 PM, drum.lu...@gmail.com wrote: Information. eg.: The schema for the tables. Why is not just adding a DEFAULT value to the users.code not an option? The customer can add their own value to the users.code column. That's why I can't have a default value.

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 5:14 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Apr 20, 2016 at 1:50 PM, melvin6925 wrote: > >> On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson >> wrote: >> >>> To Tom: >>> "it *sounds* trivial,

Fwd: Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone Original message From: "David G. Johnston" Date: 4/20/2016 16:13 (GMT-05:00) To: Melvin Davidson Subject: Re: [GENERAL] Add relcreated (timestamp) column to

Re: [GENERAL] Vacuum never completed....

btree. On 2016-04-19 06:53, Jeff Janes wrote: On Apr 19, 2016 6:37 AM, wrote: We had a server approach wraparound yesterday on a 9.3 box. I restarted in single user mode and only one table was above autovacuum_freeze_max_age. The dataset was ~750G. We left vacuum running

Re: [GENERAL] Initdb --data-checksums by default

On Wed, Apr 20, 2016 at 3:43 AM, Alex Ignatov wrote: > What do you think about defaulting --data-checksums in initdb? > I think that ZFS storing my database files already does this and can correct for it using replicated copies, so why do I need a second layer of

Re: [GENERAL] PG 9.1 on windows setup problem

On 04/20/2016 12:26 PM, Moreno Andreo wrote: Il 20/04/2016 20:58, Adrian Klaver ha scritto: On 04/20/2016 07:28 AM, Moreno Andreo wrote: Il 20/04/2016 15:34, Adrian Klaver ha scritto: On 04/20/2016 01:57 AM, Moreno Andreo wrote: Hello, I have a strange problem. I'm installing PostgreSQL

Re: [GENERAL] PG 9.1 on windows setup problem

Il 20/04/2016 20:58, Adrian Klaver ha scritto: On 04/20/2016 07:28 AM, Moreno Andreo wrote: Il 20/04/2016 15:34, Adrian Klaver ha scritto: On 04/20/2016 01:57 AM, Moreno Andreo wrote: Hello, I have a strange problem. I'm installing PostgreSQL 9.1 on Windows XP Home SP3 (don't ask!!!

Re: [GENERAL] PG 9.1 on windows setup problem

On 04/20/2016 07:28 AM, Moreno Andreo wrote: Il 20/04/2016 15:34, Adrian Klaver ha scritto: On 04/20/2016 01:57 AM, Moreno Andreo wrote: Hello, I have a strange problem. I'm installing PostgreSQL 9.1 on Windows XP Home SP3 (don't ask!!! :-)) Where did you get the binary/installer?

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

Melvin Davidson writes: > *I am a bit confused. If this is the correct list for enhancement requests, > then why is that not explicitly stated in the list description? In general, any of the major PG lists are suitable places for discussing enhancements; either here or

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wednesday, April 20, 2016, Melvin Davidson wrote: > > > > > > > > *I am a bit confused. If this is the correct list for enhancement > requests, then why is that not explicitly stated in the list description? > Then again, none of the list descriptions mention they are

[GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

*I am a bit confused. If this is the correct list for enhancement requests, then why is that not explicitly stated in the list description? Then again, none of the list descriptions mention they are are the correct ones for enhancement requests. However, Customer Feedback (

Re: [GENERAL] Enhancement Request

Rob, * Rob Brucks (rob.bru...@rackspace.com) wrote: > I'd like to propose two enhancements to the PostgreSQL code, but I'm not sure > if this is the correct mailing list. So if it's not then please let me know > where I need to post this. This is the correct place. I don't know why people

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 16:58, Alvaro Herrera wrote: Alex Ignatov wrote: Hello everyone! Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte disk bit rot is the real issue. I think that today

Re: [GENERAL] PG 9.1 on windows setup problem

Il 20/04/2016 15:34, Adrian Klaver ha scritto: On 04/20/2016 01:57 AM, Moreno Andreo wrote: Hello, I have a strange problem. I'm installing PostgreSQL 9.1 on Windows XP Home SP3 (don't ask!!! :-)) Where did you get the binary/installer? PostgreSQL official site and facing the

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

writes: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> Define "executes". You could shove those lines in via the wire protocol, >> sure, but SPI won't take them. > Now you really have me puzzled. What I provided is an extract from the log > of generated SQL commands sent to the

Re: [GENERAL] Initdb --data-checksums by default

Alex Ignatov wrote: > Hello everyone! > Today in Big Data epoch silent data corruption becoming more and more issue > to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte > disk bit rot is the real issue. > I think that today checksumming data must be mandatory set by

Re: [GENERAL] Initdb --data-checksums by default

On 04/20/2016 02:22 AM, Alex Ignatov wrote: Why do you think that common pg-users doesn't care about their data? Also why do we have wal_level=minimal fsync=on and other stuff? Because Postgres will not run without WAL files and the setting is the least you can have. Doing so you lose

Re: [GENERAL] PG 9.1 on windows setup problem

On 04/20/2016 01:57 AM, Moreno Andreo wrote: Hello, I have a strange problem. I'm installing PostgreSQL 9.1 on Windows XP Home SP3 (don't ask!!! :-)) Where did you get the binary/installer? and facing the following problem (pasted from log) - [...] Starting the

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

writes: >> From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] >> Since there are no autonomous transactions in PostgreSQL, how can you have >> BEGIN and COMMIT called from the code that is invoked by "SELECT >> my_andl_function()"? > I really don't know. But I have code that

Re: [GENERAL] Initdb --data-checksums by default

On 4/20/2016 1:00 AM, Alex Ignatov wrote: Uncorrectable read error rate ~ 10^-15- 10^-14. This error stays undetected and uncorrectable. what are your units here? disk IO is done by blocks, and the CRCs and trellis codes used by the disk drives for error detection and correction are

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 12:27, Andreas Joseph Krogh wrote: På onsdag 20. april 2016 kl. 11:22:33, skrev Alex Ignatov >: [snip] Why do you think that common pg-users doesn't care about their data? Did I say that? Also why do we

[GENERAL] How does postgresql jdbc driver implement prepared batch?

Hi, As document said, in the extended query protocol, "The query string contained in a Parse message cannot include more than one SQL statement". So to support batch in prepared statement, I think the only way is to determine the batch size in advance and then create the appropriate prepared

Re: [GENERAL] Initdb --data-checksums by default

På onsdag 20. april 2016 kl. 11:22:33, skrev Alex Ignatov < a.igna...@postgrespro.ru >: [snip] Why do you think that common pg-users doesn't care about their data?   Did I say that?   Also why do we have wal_level=minimal fsync=on and other stuff?   To make certain

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 12:10, Andreas Joseph Krogh wrote: På onsdag 20. april 2016 kl. 11:02:31, skrev Alex Ignatov >: On 20.04.2016 11:40, Andreas Joseph Krogh wrote: På onsdag 20. april 2016 kl. 10:33:14, skrev Alex Ignatov

Re: [GENERAL] Initdb --data-checksums by default

På onsdag 20. april 2016 kl. 11:02:31, skrev Alex Ignatov < a.igna...@postgrespro.ru >:   On 20.04.2016 11:40, Andreas Joseph Krogh wrote: På onsdag 20. april 2016 kl. 10:33:14, skrev Alex Ignatov < a.igna...@postgrespro.ru >: On

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 11:40, Andreas Joseph Krogh wrote: På onsdag 20. april 2016 kl. 10:33:14, skrev Alex Ignatov >: On 20.04.2016 11:29, Devrim Gündüz wrote: > Hi, > > On Wed, 2016-04-20 at 10:43 +0300, Alex Ignatov

[GENERAL] PG 9.1 on windows setup problem

Hello, I have a strange problem. I'm installing PostgreSQL 9.1 on Windows XP Home SP3 (don't ask!!! :-)) and facing the following problem (pasted from log) - [...] Starting the database server... Executing cscript //NoLogo "C:\PGSql\installer\server\startserver.vbs"

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

> From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] > > > > I am attempting to create a new language implementation. The > > > > language is Andl (andl.org), so the handler is plandl. > > > > This is a question about executing SPI queries from inside plandl. > > > > > > > > The documentation

Re: [GENERAL] Initdb --data-checksums by default

På onsdag 20. april 2016 kl. 10:33:14, skrev Alex Ignatov < a.igna...@postgrespro.ru >: On 20.04.2016 11:29, Devrim Gündüz wrote: > Hi, > > On Wed, 2016-04-20 at 10:43 +0300, Alex Ignatov wrote: >> Today in Big Data epoch silent data corruption becoming more

Re: [GENERAL] Enhancement Request

On Wed, Apr 20, 2016 at 3:47 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Apr 19, 2016 at 5:59 PM, Melvin Davidson > wrote: > >> >> Enhancement or feature requests should probably go to *Custormer >> Feedback

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 11:29, Devrim Gündüz wrote: Hi, On Wed, 2016-04-20 at 10:43 +0300, Alex Ignatov wrote: Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte disk bit rot is the real issue. I think

Re: [GENERAL] Initdb --data-checksums by default

Hi, On Wed, 2016-04-20 at 10:43 +0300, Alex Ignatov wrote: > Today in Big Data epoch silent data corruption becoming more and more  > issue to afraid of. With uncorrectable read error rate ~ 10^-15  on  > multiterabyte disk bit rot is the real issue. > I think that today checksumming data  must

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 10:58, Michael Paquier wrote: On Wed, Apr 20, 2016 at 4:43 PM, Alex Ignatov wrote: Hello everyone! Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 10:47, John R Pierce wrote: On 4/20/2016 12:43 AM, Alex Ignatov wrote: Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15on multiterabyte disk bit rot is the real issue. are not those

Re: [GENERAL] Initdb --data-checksums by default

On Wed, Apr 20, 2016 at 4:43 PM, Alex Ignatov wrote: > Hello everyone! > Today in Big Data epoch silent data corruption becoming more and more issue > to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte > disk bit rot is the real issue. > I think

Re: [GENERAL] Initdb --data-checksums by default

On 4/20/2016 12:43 AM, Alex Ignatov wrote: Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15on multiterabyte disk bit rot is the real issue. are not those uncorrectable errors detected by the disk hardware ?

[GENERAL] Initdb --data-checksums by default

Hello everyone! Today in Big Data epoch silent data corruption becoming more and more issue to afraid of. With uncorrectable read error rate ~ 10^-15 on multiterabyte disk bit rot is the real issue. I think that today checksumming data must be mandatory set by default. Only if someone

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

David Bennett wrote: > > From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] > > > > > I am attempting to create a new language implementation. The language > > > is Andl (andl.org), so the handler is plandl. > > > This is a question about executing SPI queries from inside plandl. > > > > > > The

Re: [GENERAL] Enhancement Request

On 4/20/2016 12:28 AM, Albe Laurenz wrote: >Enhancement or feature requests should probably go to Custormer Feedback > I didn't know that site. Was it ever announced? first I've heard of it, was Melvin suggesting it in another thread

Re: [GENERAL] Enhancement Request

Melvin Davidson wrote: > On Tue, Apr 19, 2016 at 4:37 PM, Rob Brucks wrote: >> I'd like to propose two enhancements to the PostgreSQL code, but I'm not >> sure if this is the >> correct mailing list. So if it's not then please let me know where I need >> to post this.