Re: [HACKERS] delta relations in AFTER triggers

On Mon, Sep 1, 2014 at 9:09 PM, Tom Lane wrote: > OTOH, I agree with Kevin that the things we're talking about are > lightweight relations not variables. My worry is that PL/pgSQL and Postgres's SQL dialect is turning into a Frankenstein monster with many ways to do the same thing, each having di

[HACKERS] Re: 答复: [HACKERS] why after increase the hash table partitions, TPMC decrease

On Tue, Sep 2, 2014 at 5:20 PM, Xiaoyulei wrote: > > I already modified MAX_SIMUL_LWLOCKS to make sure it is enough. Okay. > > > Total RAM is 130G, and I set shared_buffers 16G, CPU and IO is not full. 50% CPUs are idle. As far as I understand, benchmarkSQL measures an OLTP workload performance

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson wrote: The common use-case I have in mind is when you have a function which takes some kind of ID as an input param, which maps to a primary key in some table, which you want to update. In that case FOUND works just fine. A primary key v

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 4:15 PM, Joel Jacobson wrote: I don't like rebranding the PERFORM command, as that would require all existing code with PERFORM commands to be changed. I'm not saying the suggested syntax is perfect, but PERFORM should be euthanized anyway. Or at least the need for it; perhaps there

Re: [HACKERS] PL/pgSQL 2

Joel Jacobson wrote: > On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner wrote: >> Joel Jacobson wrote: >> >>> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify >>> exactly 1 row, as that's the most common use-case, and provide >>> alternative syntax to modify multiple or zero rows. >>

Re: [HACKERS] PL/pgSQL 2

On 2014-09-02 10:21:50 -0400, Tom Lane wrote: > Marko Tiikkaja writes: > > For example: > > > UPDATE foo WHERE bar = 1; -- must affect exactly one row > > PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows > > FWIW, I agree with the position that this would be a completely wrong

Re: [HACKERS] ALTER SYSTEM RESET?

On 09/02/2014 04:12 PM, Alvaro Herrera wrote: > Fujii Masao wrote: >> On Mon, Sep 1, 2014 at 10:54 PM, Amit Kapila wrote: >>> On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao wrote: On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila wrote: > On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao

Re: [HACKERS] PL/pgSQL 2

Marko Tiikkaja writes: > For example: > UPDATE foo WHERE bar = 1; -- must affect exactly one row > PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows FWIW, I agree with the position that this would be a completely wrong thing to do. UPDATE should work like it does in plain SQL.

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 3:58 PM, Marko Tiikkaja wrote: > When I've played around with the idea of fixing PL/PgSQL in my head, what I > had in mind is that UPDATE and DELETE not affecting exactly one row raises > an exception, unless PERFORM is used. PERFORM would set a special variable > (e.g. ROW

Re: [HACKERS] ALTER SYSTEM RESET?

Fujii Masao wrote: > On Mon, Sep 1, 2014 at 10:54 PM, Amit Kapila wrote: > > On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao wrote: > >> > >> On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila > >> wrote: > >> > On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao > >> > wrote: > >> >> The patch looks good to m

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 3:52 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas wrote: On 09/02/2014 04:32 PM, Joel Jacobson wrote: I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue what to

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 04:52 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas wrote: On 09/02/2014 04:32 PM, Joel Jacobson wrote: I think it's much better to make it the default behaviour in plpgsql2 than to add a new syntax to plpgsql, because then we don't have to argue wha

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas wrote: > On 09/02/2014 04:32 PM, Joel Jacobson wrote: >> I think it's much better to make it the default behaviour in plpgsql2 >> than to add a new syntax to plpgsql, >> because then we don't have to argue what to call the keyword or where to >> p

Re: [HACKERS] Patch for psql History Display on MacOSX

Noah Misch writes: > I'm with you that far. Given a patch that does not change "\s /tmp/foo" and > that makes "\s" equivalent to "\s /tmp/foo" + "\! cat /tmp/foo >/dev/tty", > back-patch by all means. No patch posted on this thread is so surgical, hence > my objection. In particular, your lates

Re: [HACKERS] On partitioning

On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote: > There is one situation where you need to be more flexible, and that is > if you ever want to support online repartitioning. To do that you have > to distinguish between "I want to insert tuple X, which partition > should it g

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 04:32 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas wrote: In the mailing list thread that you linked there, Tom suggested using "STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

Etsuro Fujita wrote: > Please find attached the updated version of the patch. I gave it a spin and could not find any undesirable behaviour, and the output of EXPLAIN ANALYZE looks like I'd expect. I noticed that you use the list length of fdw_private to check if the UPDATE or DELETE is pushed do

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

On 2014-09-02 08:37:42 -0500, k...@rice.edu wrote: > I agree completely. For day-to-day use we should use LZ4-default. For > read-only > tables, it might be nice to "archive" them with LZ4-HC for the higher > compression > would increase read speed and reduce storage space needs. I believe that

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

On Tue, Sep 02, 2014 at 10:30:11AM -0300, Arthur Silva wrote: > On Tue, Sep 2, 2014 at 9:11 AM, Rahila Syed wrote: > > > Hello, > > > > >It'd be interesting to check avg cpu usage as well > > > > I have collected average CPU utilization numbers by collecting sar output > > at interval of 10 secon

Re: [HACKERS] proposal: rounding up time value less than its unit.

Hi, I'm sorry for slow reaction. I don't care whether rounding up or down it, although this title has 'rounding up'. (I just only come up with it. I'm sorry for my imprudence) I'm thinking about a method which users get quick awareness it. Now, it's okay not to change current behavior except non

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 2:30 PM, Pavel Stehule wrote: > It is way how to do COBOL from plpgsql. I am against it. Start to develop > new language what will support fast development, but it is wrong way for > plpgsql - and It is out my interest Are you saying COBOL by default update's one row and th

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas wrote: > In the mailing list thread that you linked there, Tom suggested using > "STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error > (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What > happened to that pr

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

On Tue, Sep 2, 2014 at 9:11 AM, Rahila Syed wrote: > Hello, > > >It'd be interesting to check avg cpu usage as well > > I have collected average CPU utilization numbers by collecting sar output > at interval of 10 seconds for following benchmark: > > Server specifications: > Processors:Intel® Xe

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner wrote: > Joel Jacobson wrote: > >> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify >> exactly 1 row, as that's the most common use-case, and provide >> alternative syntax to modify multiple or zero rows. > > I just embarked on wading th

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 09:08 AM, Pavel Stehule wrote: JavaScript would actually be quite a good alternative. However, using it involves something others have objected to, namely calling SQL via a function call. It's true that plpgsql lets you call SQL commands without explicitly invokin

Re: [HACKERS] PL/pgSQL 2

Joel Jacobson wrote: > + Make UPDATE/INSERT/DELETE throw error if they didnt' modify > exactly 1 row, as that's the most common use-case, and provide > alternative syntax to modify multiple or zero rows. I just embarked on wading through the 99 messages (so far) on this thread, so my apologies i

Re: [HACKERS] PL/pgSQL 2

On 2014-09-02 14:41:03 +0200, Marko Tiikkaja wrote: > On 9/2/14 2:29 PM, Heikki Linnakangas wrote: > >In the mailing list thread that you linked there, Tom suggested using > >"STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error > >(http://www.postgresql.org/message-id/16397.1356106...

Re: [HACKERS] PL/pgSQL 2

2014-09-02 14:38 GMT+02:00 Andrew Dunstan : > > On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote: > >> >> On 02/09/14 11:34, Mark Kirkwood wrote: >> >>> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: >>> On 02/09/14 05:24, Craig Ringer wrote: > I couldn't disagree more

Re: [HACKERS] PL/PgSQL: RAISE and the number of parameters

On 09/02/2014 11:52 AM, Fabien COELHO wrote: I've changed the loop slightly. Do you find this more readable than the way the loop was previously written? It is 50% better:-) It is no big deal, but I still fail to find the remaining continue as usefull in this case. If you remove the "contin

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 08:41 AM, Ryan Pedela wrote: If PL/Javascript is a serious consideration, how will int64 and numeric be handled? Please don't top-post on the PostgreSQL lists. See Unfortunately, I think the short answer is "not very well". In theory we

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 2:29 PM, Heikki Linnakangas wrote: In the mailing list thread that you linked there, Tom suggested using "STRICT UPDATE ..." to mean that updating 0 or >1 rows is an error (http://www.postgresql.org/message-id/16397.1356106...@sss.pgh.pa.us). What happened to that proposal? http://www

Re: [HACKERS] PL/pgSQL 2

If PL/Javascript is a serious consideration, how will int64 and numeric be handled? Thanks, Ryan Pedela Datalanche CEO, co-founder www.datalanche.com rped...@datalanche.com 513-571-6837 On Tue, Sep 2, 2014 at 6:38 AM, Andrew Dunstan wrote: > > On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote: On 02/09/14 11:34, Mark Kirkwood wrote: On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki

Re: [HACKERS] PL/pgSQL 2

2014-09-02 14:16 GMT+02:00 Joel Jacobson : > On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule > wrote: > > What we can do better? > > > > 1. we can implement a conditional RAISE > > > > DELETE FROM tab WHERE xx = somevar; > > GET DIAGNOSTICS rc = ROW_COUNT; > > RAISE EXCEPTION 'some' WHEN rc <> 0;

Re: [HACKERS] PL/pgSQL 2

On 09/02/2014 03:16 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule wrote: What we can do better? 1. we can implement a conditional RAISE DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; RAISE EXCEPTION 'some' WHEN rc <> 0; It is relatively natural

Re: [HACKERS] WAL format and API changes (9.5)

On 08/19/2014 05:38 PM, Andres Freund wrote: On 2014-08-19 10:33:29 -0400, Alvaro Herrera wrote: Heikki Linnakangas wrote: Barring objections or better ideas, I'm leaning towards XLogReadBufferForRedo. WFM for me too. Although we could imo strip the 'XLog' in the beginning if we want to ma

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule wrote: > What we can do better? > > 1. we can implement a conditional RAISE > > DELETE FROM tab WHERE xx = somevar; > GET DIAGNOSTICS rc = ROW_COUNT; > RAISE EXCEPTION 'some' WHEN rc <> 0; > > It is relatively natural and we use similar construct in

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

Hello, >It'd be interesting to check avg cpu usage as well I have collected average CPU utilization numbers by collecting sar output at interval of 10 seconds for following benchmark: Server specifications: Processors:Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM: 32GB Disk

Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors

Hi Pavel, it needs a redesign of original implementation, we should to change API to > use default values with named parameters > > but it doesn't help too much (although it can be readable little bit more) > > instead row_to_json(x, false, true) > > be > > row_ro_json(x, ignore_null := true) > >

[HACKERS] 答复: [HACKERS] why after increase the hash table partitions, TPMC decrease

I already modified MAX_SIMUL_LWLOCKS to make sure it is enough. Total RAM is 130G, and I set shared_buffers 16G, CPU and IO is not full. 50% CPUs are idle. So I think maybe pg is blocked by some place in itself. 发件人: Amit Kapila [mailto:amit.kapil...@gmail.com] 发送时间: 2014年9月2日 19:31 收件人: Xiaoyu

Re: [HACKERS] why after increase the hash table partitions, TPMC decrease

On Tue, Sep 2, 2014 at 2:09 PM, Xiaoyulei wrote: > > > > We use benchmarksql to start tpcc test in postgresql 9.3.3. > > Before test we set benchmarksql client number about 800. And we increase the hash partitions from 16 to 1024 , in order to reduce the hash partition locks competition. > > We ex

Re: [HACKERS] add line number as prompt option to psql

On 2014-09-02 12:34:12 +0900, Sawada Masahiko wrote: > On Tue, Sep 2, 2014 at 11:12 AM, Andres Freund wrote: > > I've now used up a perfectly good glass of wine for this, so this is it > > for today ;) > > > > Thank you for updating the patch! > I tested it. > These fix looks good to me :) Commi

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 12:46, Marko Tiikkaja wrote: On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote: If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude mo

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 11:40 AM, Álvaro Hernández Tortosa wrote: If we are to have another plpgsql-like language (like plpgsql2) and we could design it so it would attract many many users (let's not forget that Oracle may have around two orders of magnitude more users than pg), that would benefit us all

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 11:56, Pavel Stehule wrote: 2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa >: On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa mailto:a...@nosys.es>>: On 02/09/14 05:24, Craig Ringer wro

Re: [HACKERS] PL/pgSQL 2

2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa : > > On 02/09/14 11:31, Pavel Stehule wrote: > > > > > 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa : > >> >> On 02/09/14 05:24, Craig Ringer wrote: >> >>> I couldn't disagree more. >>> >>> If we were to implement anything, it'd be PL/PSM

Re: [HACKERS] postgres_fdw behaves oddly

(2014/09/01 20:15), Etsuro Fujita wrote: > While working on [1], I've found that postgres_fdw behaves oddly: > > postgres=# create foreign table ft (a int) server loopback options > (table_name 't'); > CREATE FOREIGN TABLE > postgres=# select tableoid, * from ft; > tableoid | a > --+---

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 11:44, Pavel Stehule wrote: For 9.4, we have the media already saying "Postgres has NoSQL capabilities" (which is only partially true). For x.y we could have the media saying "Postgres adds Oracle compatibility" (which would be only partially true). But that

Re: [HACKERS] PL/pgSQL 2

2014-09-02 11:44 GMT+02:00 Álvaro Hernández Tortosa : > > On 02/09/14 11:34, Mark Kirkwood wrote: > >> On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: >> >>> >>> On 02/09/14 05:24, Craig Ringer wrote: >>> I couldn't disagree more. If we were to implement anything, it'd be PL/PSM

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa >: On 02/09/14 05:24, Craig Ringer wrote: I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki

Re: [HACKERS] PL/pgSQL 2

2014-09-02 11:40 GMT+02:00 Álvaro Hernández Tortosa : > > On 02/09/14 06:40, Tom Lane wrote: > >> Craig Ringer writes: >> >>> If someone came up with a convincing PL/SQL compatibility layer then >>> it'd be worth considering adopting - when it was ready. But of course, >>> anyone who does the wor

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 11:34, Mark Kirkwood wrote: On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything

Re: [HACKERS] PL/pgSQL 2

2014-09-02 11:34 GMT+02:00 Marko Tiikkaja : > On 9/2/14 11:04 AM, Pavel Stehule wrote: > >> It is relatively natural and we use similar construct in CONTINUE >> statement. >> >> 2. What can be next? We can implement some idiom (shortcut) for GET >> DIAGNOSTICS >> >> DELETE FROM tab WHERE xx = some

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 06:40, Tom Lane wrote: Craig Ringer writes: If someone came up with a convincing PL/SQL compatibility layer then it'd be worth considering adopting - when it was ready. But of course, anyone who does the work for that is quite likely to want to sell it to cashed-up Oracle users loo

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 21:25, Álvaro Hernández Tortosa wrote: On 02/09/14 05:24, Craig Ringer wrote: I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything else the SQL committee has brought forth,

Re: [HACKERS] PL/pgSQL 2

On 9/2/14 11:04 AM, Pavel Stehule wrote: It is relatively natural and we use similar construct in CONTINUE statement. 2. What can be next? We can implement some idiom (shortcut) for GET DIAGNOSTICS DELETE FROM tab WHERE xx = somevar; RAISE EXCEPTION 'some' WHEN AFFECTED_ROW_COUNT <> 1; Yes, a

Re: [HACKERS] PL/pgSQL 2

2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa : > > On 02/09/14 05:24, Craig Ringer wrote: > >> I couldn't disagree more. >> >> If we were to implement anything, it'd be PL/PSM >> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and >> quirky as anything else the SQL committe

Re: [HACKERS] PL/pgSQL 2

On 02/09/14 05:24, Craig Ringer wrote: I couldn't disagree more. If we were to implement anything, it'd be PL/PSM (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and quirky as anything else the SQL committee has brought forth, but it's at least a standard(ish) language. So

Re: [HACKERS] PL/pgSQL 2

2014-09-01 11:04 GMT+02:00 Joel Jacobson : > Hi, > > For those of you who use PL/pgSQL every day, I'm quite certain you all > feel there are a number of things you would like to change in the language, > but realize it cannot be achieved without possibly breaking compatibility, > at least in theor

Re: [HACKERS] PL/PgSQL: RAISE and the number of parameters

Hello Marko, I've changed the loop slightly. Do you find this more readable than the way the loop was previously written? It is 50% better:-) It is no big deal, but I still fail to find the remaining continue as usefull in this case. If you remove the "continue" line and invert the condit

[HACKERS] why after increase the hash table partitions, TPMC decrease

We use benchmarksql to start tpcc test in postgresql 9.3.3. Before test we set benchmarksql client number about 800. And we increase the hash partitions from 16 to 1024 , in order to reduce the hash partition locks competition. We expect that after increase the number of partitions, reduces lock

Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?

Hi, I also tried this. This looks nice but seems a bit difficult to find a rasonable behavior. > I have worked on that patch a little more. So now I have functional patch > (although still WIP) attached. The feature works as following: > > - Added a boolean parameter "only_temp_files" to pg_table

Re: [HACKERS] PL/pgSQL 2

On Tue, Sep 2, 2014 at 8:26 AM, Heikki Linnakangas wrote: > On 09/02/2014 09:06 AM, Joel Jacobson wrote: >> For me, the most important is to not break *most* of existing plpgsql >> code, but it's OK to break *some*. >> And when breaking it, it should be trivial to rewrite it to become >> compatibl

Re: [HACKERS] ALTER SYSTEM RESET?

On Mon, Sep 1, 2014 at 10:54 PM, Amit Kapila wrote: > On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao wrote: >> >> On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila >> wrote: >> > On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao >> > wrote: >> >> The patch looks good to me. One minor comment is; probably y

Re: [HACKERS] Concurrently option for reindexdb

On Tue, Sep 2, 2014 at 1:06 PM, Craig Ringer wrote: > On 09/02/2014 11:10 AM, Sawada Masahiko wrote: >> The currently patch dose not hack catalog, just create new index >> concurrently and >> swap them. >> So, It is supporting only UNIQUE index, I think. > > UNIQUE indexes, but not a UNIQUE constr

<    1   2