Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 5:46 AM, Craig Ringer cr...@2ndquadrant.com wrote: My point is that weeks can be spent just arguing about whether you should have a variable-delimiter ($variable) or not, how syntax should look, etc. Imagine how long it'd take to get a new language syntax agreed upon? I would guess about a year. You jumped in to say that you thought that: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; was is exactly why we need a new language and that All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. But you haven't said HOW you propose to fix this one case. Show me. How do you want this to look? The user requirement is Execute a SELECT against a table whose name is provided at runtime, selecting a column or set of columns whose names are provided at runtime, with literals substituted as placement parameters. The above is ugly. Fine, not arguing. Show me what you want instead. You're happy to say how much you dislike PL/PgSQL, but I haven't seen a concrete proposal on how you want something new to look. That would be a useful and constructive start, as we could then examine, point-by-point, how/if those needs can be met in PL/PgSQL. If they can't then you'd have a more convincing argument for a new version than PL/PgSQL sucks. I've *never* said PL/pgSQL sucks. I *love* PL/pgSQL, seriously. I write code for many hours a day in the language. I don't even want to change much. My wishlist consists mostly of things which makes the language more secure. Currently it's a pain to verify your data operations do exactly what you requested. I would guess most novice developers don't understand this, and by mistake write insecure code. I don't want any OO. I don't want PL/SQL or PL/PSM. I'm a happy camper with PL/pgSQL. That said, *if* we now have a one-shot opportunity of possibly breaking a bit of compatibility for a minority of current code, motivated by the introduction of new important features not possible without plpgsql2, *then* let's make the best of that opportunity. I don't find myself selecting from a table which table name I don't know the name when writing the code, so I'm not pariticulary interested in prodiving a syntax for that use case, but I'm not against the feature if others need it, even if it would possibly increase the lines of code of existing plpgsql code which needs to be modified to remain compatible by X %. Given the needed diff between plpgsql and plpgsql2 for the changes I'm mostly interested in would probably be quite small, I'm in favour of Tom's suggestion of: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. That fits perfectly for my needs, as I don't want to change much. But even if we find we want to make larger mostly-compatible changes, maybe that also can be implemented using the same approach. 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 compatible. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 09:06 AM, Joel Jacobson wrote: Given the needed diff between plpgsql and plpgsql2 for the changes I'm mostly interested in would probably be quite small, I'm in favour of Tom's suggestion of: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. That fits perfectly for my needs, as I don't want to change much. But even if we find we want to make larger mostly-compatible changes, maybe that also can be implemented using the same approach. 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 compatible. I think the next step would be to list all the things you don't like with current PL/pgSQL, and write down how you would want them to work if you were starting with a clean slate. Let's see how wide the consensus is that the new syntax/behavior is better than what we have now. We can then start thinking how to best adapt them to the current PL/pgSQL syntax and codebase. Maybe with pragmas, or new commands, or deprecating the old behavior; the best approach depends on the details, and how widely desired the new behavior is, so we need to see that first. I'd suggest collecting the ideas on a wiki page, and once you have some concrete set of features and syntax there, start a new thread to discuss them. Others will probably have other features they want, like the simpler DROP TABLE ? thing. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On Thu, Aug 28, 2014 at 8:05 PM, Peter Geoghegan p...@heroku.com wrote: I realized that I missed a few cases here. For one thing, the posted patch fails to arrange for the UPDATE post-parse-analysis tree representation to go through the rewriter stage (on the theory that user-defined rules shouldn't be able to separately affect the auxiliary UPDATE query tree), but rewriting is at least necessary so that rewriteTargetListIU() can expand a SET val = DEFAULT targetlist, as well as normalize the ordering of the UPDATE's tlist. Separately, the patch fails to defend against certain queries that ought to be disallowed, where a subselect is specified with a subquery expression in the auxiliary UPDATE's WHERE clause. Attached revision fixes all of these issues. I've added regression tests for each bug, too, although all changes are rebased into my original commits. I decided to explicitly rely on a simpler approach to VACUUM interlocking. I no longer bother holding on to a buffer pin for a period longer than the period that associated value locks are held, which was something I talked about at the start of this thread. There is a note on this added to the nbtree README, just after the master branch's current remarks on B-Tree VACUUM interlocking. I've also pushed the responsibility for supporting this new feature on foreign tables onto FDWs themselves. The only writable FDW we currently ship, postgres_fdw, lacks support for the new feature, but this can be revisited in due course. My impression is that the task of adding support is not quite a straightforward matter of adding a bit more deparsing logic, but also isn't significantly more difficult than that. -- Peter Geoghegan 0001-Make-UPDATE-privileges-distinct-from-INSERT-privileg.patch.gz Description: GNU Zip compressed data 0004-Internal-documentation-for-INSERT-.-ON-CONFLICT-UPDA.patch.gz Description: GNU Zip compressed data 0003-Tests-for-INSERT-.-ON-CONFLICT-UPDATE-IGNORE.patch.gz Description: GNU Zip compressed data 0002-Support-INSERT-.-ON-CONFLICT-UPDATE-IGNORE.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes
On Wed, Aug 27, 2014 at 11:52 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Aug 26, 2014 at 8:14 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed rahilasye...@gmail.com wrote: Hello, Thank you for comments. Could you tell me where the patch for single block in one run is? Please find attached patch for single block compression in one run. Thanks! I ran the benchmark using pgbench and compared the results. I'd like to share the results. [RESULT] Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off202.0201.5 on6051.06053.0 pglz3543.03567.0 lz43344.03485.0 snappy3354.03449.5 Latency average during the benchmark. Unit is ms. MultipleSingle off19.119.0 on55.357.3 pglz45.045.9 lz444.244.7 snappy43.443.3 These results show that FPW compression is really helpful for decreasing the WAL volume and improving the performance. Yeah, those look like good numbers. What happens if you run it at full speed, without -R? OK, I ran the same benchmark except -R option. Here are the results: [RESULT] Throughput in the benchmark. MultipleSingle off2162.62164.5 on891.8895.6 pglz1037.21042.3 lz41084.71091.8 snappy1058.41073.3 Latency average during the benchmark. Unit is ms. MultipleSingle off29.629.6 on71.771.5 pglz61.761.4 lz459.058.6 snappy60.559.6 Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off948.0948.0 on7675.57702.0 pglz5492.05528.5 lz45494.55596.0 snappy5667.05804.0 pglz vs. lz4 vs. snappy In this benchmark, lz4 seems to have been the best compression algorithm. It caused best performance and highest WAL compression ratio. Multiple vs. Single WAL volume with Multiple was smaller than that with Single. But the throughput was better in Single. So the Multiple is more useful for WAL compression, but it may cause higher performance overhead at least in current implementation. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes
On Thu, Aug 28, 2014 at 12:46 AM, Arthur Silva arthur...@gmail.com wrote: Em 26/08/2014 09:16, Fujii Masao masao.fu...@gmail.com escreveu: On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed rahilasye...@gmail.com wrote: Hello, Thank you for comments. Could you tell me where the patch for single block in one run is? Please find attached patch for single block compression in one run. Thanks! I ran the benchmark using pgbench and compared the results. I'd like to share the results. [RESULT] Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off202.0201.5 on6051.06053.0 pglz3543.03567.0 lz43344.03485.0 snappy3354.03449.5 Latency average during the benchmark. Unit is ms. MultipleSingle off19.119.0 on55.357.3 pglz45.045.9 lz444.244.7 snappy43.443.3 These results show that FPW compression is really helpful for decreasing the WAL volume and improving the performance. The compression ratio by lz4 or snappy is better than that by pglz. But it's difficult to conclude which lz4 or snappy is best, according to these results. ISTM that compression-of-multiple-pages-at-a-time approach can compress WAL more than compression-of-single-... does. [HOW TO BENCHMARK] Create pgbench database with scall factor 1000. Change the data type of the column filler on each pgbench table from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's gen_random_uuid() in order to avoid empty column, e.g., alter table pgbench_accounts alter column filler type text using gen_random_uuid()::text After creating the test database, run the pgbench as follows. The number of transactions executed during benchmark is almost same between each benchmark because -R option is used. pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared checkpoint_timeout is 5min, so it's expected that checkpoint was executed at least two times during the benchmark. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers It'd be interesting to check avg cpu usage as well. Yep, but I forgot to collect those info... Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Immediate standby promotion
On Mon, Sep 1, 2014 at 4:44 PM, Fujii Masao masao.fu...@gmail.com wrote:
On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila amit.kapil...@gmail.com
wrote:
I think there is one downside as well for this proposal that
apart from data loss, it can lead to uncommitted data occupying
space in database which needs to be later cleaned by vacuum.
This can happen with non-immediate promote as well, but the
chances with immediate are more. So the gain we got by doing
immediate promotion can lead to slow down of operations in some
cases. It might be useful if we mention this in docs.
Yep, the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data of VACUUM. But, OTOH,
I think that the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data which will generate
garbage data.
This seems arguable, because immediate promotion won't allow
WAL data to be replayed completely which means more chance
that only partial data of transactions will be replayed and commit
for those transactions won't get replayed, so it can lead to garbage
data.
So I'm not sure if it's worth adding that note to the doc.
No issues, I just want to bring this point to your notice so that if
you think it is important enough that we can mention it then we
can update the docs else leave it.
Few comments about patch:
1.
On standby we will see below message:
LOG: received promote request
User will always see above message irrespective of whether it
is immediate promote or any other mode of promote. I think it will
be better to distinguish between different modes and display the
appropriate message.
Agreed. So I'm thinking to change the code as follows.
if (immediate_promote)
ereport(LOG, (errmsg(received immediate promote request)));
else
ereport(LOG, (errmsg(received promote request)));
This seems fine to me.
Or we should name the normal promotion?
No need.
2.
StartupXLOG()
{
..
+ if (immediate_promote)
+ break;
..
}
Why are you doing this check after pause
(recoveryApplyDelay/recoveryPausesHere) for recovery?
Why can't we do it after ReadRecord()?
We can do that check either after ReadRecord() or after pause.
I preferred to add the check after pause because immediate promotion
would be likely to be requested while recovery is being paused.
In this case, if we do that check after ReadRecord(), we need to read
one more WAL record that actually we don't need.
Okay, but for that you need to make sure that pause can detect
promotion request.
BTW, in the current patch, when immediate promotion is requested while
recovery is being paused, the recovery keeps being paused until it's
manually resumed. But immediate promotion should cause even paused
recovery to end immediately?
Yeap, I also think so.
Another issue with immediate promotion is that currently if primary server
is continuously sending the data, then standby could not detect --immediate
promote request and the reason seems to be below code:
WaitForWALToBecomeAvailable()
{
...
{
/* just make sure source info is correct... */
readSource = XLOG_FROM_STREAM;
XLogReceiptSource = XLOG_FROM_STREAM;
return true;
}
..
if (CheckForStandbyTrigger())
}
Basically we won't check for promote request if the data is available.
I have even reproduced this by below test case:
Primary (session-1) -
1. Create table t1 (c1 int, c2 char(500)) with (fillfactor = 10);
Standby -
2. Configure and start standby
3. Just connect with one client
Primary (session-1) -
4. insert into t1 values (generate_series(1,10), 'a');
From another window, run command:
5. pg_ctl promote --immediate -D ..\..\Database1
Run step-4 and step-5 at the same time.
Currently standby is promoted only after insert operation
in step-4 is finished which seems to be wrong.
Apart from above issue, I have one question for you regarding
this feature, currently the patch supports immediate promotion
via pg_ctl promote, however we have another mechanism (trigger_file)
which you have not enhanced to support this new feature. Is there
any reason for same?
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Concurrently option for reindexdb
On Tue, Sep 2, 2014 at 1:06 PM, Craig Ringer cr...@2ndquadrant.com 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 constraint backed by a UNIQUE index, or a PRIMARY KEY constraint backed by a UNIQUE index. You can use ALTER TABLE ... DROP CONSTRAINT ... ADD PRIMARY KEY USING INDEX ... for them. I'm not sure how to rebuild the index which other object like foreign key depends on, though. This patch contains some limitation. Also I'm thinking to implement to handle these cases. My understanding from the prior discussion is that any satisfactory solution to those problems would also make it possible to support REINDEX CONCURRENTLY natively. Agreed. We will need to back to Sawada's proposal only when we fail to apply REINDEX CONCURRENTLY patch again. I hope that will not happen. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER SYSTEM RESET?
On Mon, Sep 1, 2014 at 10:54 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao masao.fu...@gmail.com wrote: The patch looks good to me. One minor comment is; probably you need to update the tab-completion code. Thanks for the review. I have updated the patch to support tab-completion. As this is a relatively minor change, I will mark it as Ready For Committer rather than Needs Review. Thanks for updating the patch! One more minor comment is; what about applying the following change for the tab-completion for RESET ALL? This causes the tab-completion of even ALTER SYSTEM SET to display all and that's strange. But the tab-completion of SET has already had the same problem. So I think that we can live with that. Right and I have checked that behaviour is same for other similar statements like Alter Database database_name SET config_var or Alter User user_name SET config_var. So, the change made by you is on similar lines. OK. Applied. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 8:26 AM, Heikki Linnakangas hlinnakan...@vmware.com 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 compatible. I think the next step would be to list all the things you don't like with current PL/pgSQL, and write down how you would want them to work if you were starting with a clean slate. Let's see how wide the consensus is that the new syntax/behavior is better than what we have now. We can then start thinking how to best adapt them to the current PL/pgSQL syntax and codebase. Maybe with pragmas, or new commands, or deprecating the old behavior; the best approach depends on the details, and how widely desired the new behavior is, so we need to see that first. I'd suggest collecting the ideas on a wiki page, and once you have some concrete set of features and syntax there, start a new thread to discuss them. Others will probably have other features they want, like the simpler DROP TABLE ? thing. Excellent idea, I'm on it! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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_tablespace.spcoptions;
- This parameter can be set to true only during CREATE TABLESPACE, not on
ALTER TABLESPACE (I have thought of ways of implementing the latter, and
I'd like to discuss it more latter);
- On the creation of relations, it is checked if it is a
temporary-tablespace, and an error occurs when it is and the relation is
not temporary (temp table or index on a temp table);
- When a temporary file (either relation file or sort/agg file) is created
inside a temporary-tablespace, the entire directories structure is created
on-demand (e.g. if pg_tblspc/oid/TABLESPACE_VERSION_DIRECTORY is
missing, it is created on demand) it is done on
OpenTemporaryFileInTablespace, at fd.c (I wonder if shouldn't we do that
for any tablespace) and on TablespaceCreateDbspace, at tablespace.c.
I still haven't change documentation, as I think I need some insights about
the changes. I have some more thoughts about the syntax and I still think
that TEMP LOCATION syntax is better suited for this patch. First because
of the nature of the changes I made, it seems more suitable to a column on
pg_tablespace rather than an option. Second because no ALTER is available
(so far) and I think it is odd to have an option that can't be changed.
Third, I think TEMP keyword is more clear and users can be more used to
it.
Thoughts?
I'm going to add the CF app entry next. Could I get some review now or
after discussion about how things are going (remember I'm a newbie on this,
so I'm a little lost)?
Here is some random comments.
1. I think some users may want to store the temp tablespace in
specially created subdirectory, like this.
| =# CREATE TABLESPACE hoge LOCATION
'/mount_point_of_nonpersist_device/temptblspc1'
| WITH (only_temp_files = true);
I saw the following message for create table after restarting
after rm -r /mount...ice/*.
| =# create temp table thoge (a int) tablespace hoge;
| ERROR: could not create directory pg_tblspc/16435/PG_9.5_201408162: No
such file or directory
Multiple-depth mkdir would be needed.
2. Creating a temporary table in a tablespace with
(only_temp_files = false) after erasing the directory then
restarting the server failed showing me the following message
only for the first time,
| =# create temp table thoge (a int) tablespace hoge;
| ERROR: could not create directory pg_tblspc/16435/PG_9.5_201408162/13004:
Success
Unpatched head seems always showing 'No such file or directory'
from the first time for the case.
3. I saw the following error message during startup after
deleting the tablespace directory for the only-temp tablespace.
| $ postgres
| LOG: database system was shut down at 2014-09-02 16:54:39 JST
*| LOG: could not open tablespace directory
pg_tblspc/16435/PG_9.5_201408162: No such file or directory
| LOG: autovacuum launcher started
| LOG: database system is ready to accept connections
I think the server should refrain from showing this message for
laking of the directories for only-temp teblespaces.
4. You inhibited the option only_temp_files from ALTER'ing from
false to true but pg_tablesspace.spcoptions unfortunately can be
changed directly. Other reloptions for all objects seems not so
harmful.
| =# update pg_tablespace set spcoptions = '{only_temp_files=true}' where
spcname = 'hoge';
Are we allowed to store such a kind of option as reoptions? Or a
result from such a bogus operation should be ignored? Or do we
ought to protect spcoptions from direct modification? Or ...
Any Thoughts?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[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 competition, TPMC should be increased. But the test results on the contrary, after modified to 1024, TPMC did not increase, but decrease. Why such result? We modify the following macro definition: NUM_BUFFER_PARTITIONS 1024 LOG2_NUM_PREDICATELOCK_PARTITIONS 10 LOG2_NUM_LOCK_PARTITIONS 10
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 condition, it works exactly the same, so it is just one useless instruction within that loop. From a logical point of view the loop is looking for '%' and then check whether the next char is '%' or not, so the straightforward code helps my understanding as it does exactly that, and the continue is just an hindrance to comprehension. Note that I would buy it if it helped avoid indenting further a significant portion of complex code, but this is not the case here. [doc] I've incorporated these changes into this version of the patch, with small changes. Ok. With elog(ERROR, ..) it's still reported, but the user isn't fooled into thinking that the error is to be expected, and hopefully we would see a bug report. If it's impossible to tell the two errors apart, we might have subtly broken code carried around for who knows how long. Ok. In that case, it would make sense to keep distinct wordings of both exceptions in the execution code, so that they also can be set apart, i.e. keep the too many/few somewhere in the error? -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 11:04 GMT+02:00 Joel Jacobson j...@trustly.com: 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 theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + 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. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors last paragraph is some what I dislike on your proposal. Why: plpgsql is relative good mix of simplified ADA -- there are no too complex statement, no too much keywords, it is language that is simple to learn. Second part of mix is PostgreSQL SQL. It does same things what does in plan SQL. Your proposal change it. It is not good idea. Exactly clean solution is possible now DELETE FROM tab WHERE xx = somevar; GET DIAGNOSTICS rc = ROW_COUNT; IF rc 1 THEN RAISE EXCEPTION END IF; It is absolutely clean, absolutely readable. But it is verbose - yes, agree, maybe too much. But verbosity is basic stone of ADA and plpgsql too. It is what I like on plpgsql. 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 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; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. Regards Pavel p.s. I dislike some flags to SQL statements .. like STRICT it increase a complexity of PL parser, and it increase a distance between SQL and PLPGSQL SQL. These are small changes, probably possible with just a few hundred lines of code in total, which also should be the ambition, as larger changes would never survive during time as it would require too much efforts to keep up with the main project. Secondly, I trust plpgsql mainly because it's being used by a lot of people in a lot of production systems, the same would not hold true for plpgsql2 for the first years of existence, so we who would use it in production systems must understand every single line of code changed and feel the risk of possible bugs and their impact are within acceptable boundaries. I can probably think of a few more things, but these are the major annoyances. Please share your wish list of things you would want in plpgsql2 which are not possible to implement in plpgsql because they could possibly break compatibility. Regards, Joel
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 we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Creating a new language when there are already many existing contenders is absolutely nonsensical. Other than PL/PSM the only thing that'd make any sense would be to *pick a suitable existing language* like Lua or JavaScript and bless it as a supported, always-available, in-core language runtime that's compiled in by default. That is in my opinion a way more sensible choice. To bless PL/JavaScript as an in-core language would be a very wise choice. Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: 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 we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, Creating a new language when there are already many existing contenders is absolutely nonsensical. Other than PL/PSM the only thing that'd make any sense would be to *pick a suitable existing language* like Lua or JavaScript and bless it as a supported, always-available, in-core language runtime that's compiled in by default. That is in my opinion a way more sensible choice. To bless PL/JavaScript as an in-core language would be a very wise choice. Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 special variable would be closer to how I would prefer to access the row count. 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; I really, really don't like the idea of turning regular SQL statements into something slightly different based on comments around (or inside) the query. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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, but it's at least a standard(ish) language. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. Regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 06:40, Tom Lane wrote: Craig Ringer cr...@2ndquadrant.com 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 looking to save a few hundred grand on per-CPU licensing. As a case in point, EDB have spent quite a few man-years on their Oracle compatibility layer; and it's still not a terribly exact match, according to my colleagues who have looked at it. So that is a tarbaby I don't personally care to touch ... even ignoring the fact that cutting off EDB's air supply wouldn't be a good thing for the community to do. regards, tom lane OK, so this compatibility layer is tough. Knew that already ;) But on the other side, the syntax is similar to plpgsql, right? So what about just having a compatible syntax? It would be the first step to that compatibility layer, which could -or could not- be a long-term goal for postgres (having the whole layer). I don't buy that having that would cut EDB's air supply. They're doing great, and they know how to take care of themselves, I'm sure ;) Besides that, competition is always positive, and I'm sure they'd be more benefited than harmed by postgres having that layer. 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 greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. 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 brings a lot of users to postgres, and that helps us all. And also it could serve as a motivation point to implement those in-core missing features, too, that Oracle has. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. My 4 (already) cents, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:34 GMT+02:00 Marko Tiikkaja ma...@joh.to: 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 special variable would be closer to how I would prefer to access the row count. I am not against. We have FOUND, we can have AFFECTED_ROW_COUNT or something else. ROW_COUNT is probably wide used as variable. This style can be simply implemented. Pavel 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; I really, really don't like the idea of turning regular SQL statements into something slightly different based on comments around (or inside) the query. it can be something else than comment. For me, it is really futuristic, but it has more potential than using some specialized keywords inside SQL statement. More, we can mix it with #option - be global for function. .marko
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 else the SQL committee has brought forth, but it's at least a standard(ish) language. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. I agree. I think I haven't suggested adding new syntax to pl/pgsql. But having its syntax similar to ADA is IMHO not something good. I'm sure few prospective postgres users would be compelled to that. They are compelled about JavaScript, python, Scala or Ruby, to name a few, but definitely not ADA. Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:40 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 06:40, Tom Lane wrote: Craig Ringer cr...@2ndquadrant.com 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 looking to save a few hundred grand on per-CPU licensing. As a case in point, EDB have spent quite a few man-years on their Oracle compatibility layer; and it's still not a terribly exact match, according to my colleagues who have looked at it. So that is a tarbaby I don't personally care to touch ... even ignoring the fact that cutting off EDB's air supply wouldn't be a good thing for the community to do. regards, tom lane OK, so this compatibility layer is tough. Knew that already ;) But on the other side, the syntax is similar to plpgsql, right? So what about just having a compatible syntax? It would be the first step to that compatibility layer, which could -or could not- be a long-term goal for postgres (having the whole layer). I don't buy that having that would cut EDB's air supply. They're doing great, and they know how to take care of themselves, I'm sure ;) Besides that, competition is always positive, and I'm sure they'd be more benefited than harmed by postgres having that layer. 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 greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. 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 brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong And also it could serve as a motivation point to implement those in-core missing features, too, that Oracle has. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. My 4 (already) cents, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 a...@nosys.es mailto:a...@nosys.es: 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 we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Álvaro
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:44 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: 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 else the SQL committee has brought forth, but it's at least a standard(ish) language. So we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ Well there is the risk that by randomly adding new syntax to PL/pgSQL we turn it in a bizarre and quirky *non standard* language. Part of the attraction of PL/pgsql is that it is Ada like - if we break that too much then...well...that would be bad. So I think a careful balance is needed, to add new features that keep the spirit of the original language. I agree. I think I haven't suggested adding new syntax to pl/pgsql. But having its syntax similar to ADA is IMHO not something good. I'm sure few prospective postgres users would be compelled to that. They are compelled about JavaScript, python, Scala or Ruby, to name a few, but definitely not ADA. SQL/PSM is mix near Modula -- like Lua But integrated JavaScript can be good idea And Lua too - it is faster than Javascript with less overhead, but with significantly less community. Pavel Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Álvaro
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
--+---
16400 | 1
(1 row)
postgres=# select tableoid, * from ft where tableoid = 16400;
tableoid | a
--+---
(0 rows)
I think that one simple way of fixing such issues would be
to consider unsafe to send to the remote a qual that contains any system
columns.
I noticed the previous patch has overdone it. Attached is an updated
version of the patch.
Thanks,
PS:
[1] https://commitfest.postgresql.org/action/patch_view?id=1386
I'll update the patch in [1] on top of this version.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***
*** 252,257 foreign_expr_walker(Node *node,
--- 252,263
if (var-varno == glob_cxt-foreignrel-relid
var-varlevelsup == 0)
{
+ /*
+ * System columns can't be sent to remote.
+ */
+ if (var-varattno 0)
+ return false;
+
/* Var belongs to foreign table */
collation = var-varcollid;
state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***
*** 20,25
--- 20,26
#include math.h
#include access/skey.h
+ #include access/sysattr.h
#include catalog/pg_class.h
#include foreign/fdwapi.h
#include miscadmin.h
***
*** 1945,1950 create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
--- 1946,1953
RelOptInfo *rel = best_path-path.parent;
Index scan_relid = rel-relid;
RangeTblEntry *rte;
+ Bitmapset *attrs_used = NULL;
+ ListCell *lc;
int i;
/* it should be a base rel... */
***
*** 1993,2008 create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
* bit of a kluge and might go away someday, so we intentionally leave it
* out of the API presented to FDWs.
*/
scan_plan-fsSystemCol = false;
for (i = rel-min_attr; i 0; i++)
{
! if (!bms_is_empty(rel-attr_needed[i - rel-min_attr]))
{
scan_plan-fsSystemCol = true;
break;
}
}
return scan_plan;
}
--- 1996,2030
* bit of a kluge and might go away someday, so we intentionally leave it
* out of the API presented to FDWs.
*/
+
+ /*
+ * Add all the attributes needed for joins or final output. Note: we must
+ * look at reltargetlist, not the attr_needed data, because attr_needed
+ * isn't computed for inheritance child rels.
+ */
+ pull_varattnos((Node *) rel-reltargetlist, rel-relid, attrs_used);
+
+ /* Add all the attributes used by restriction clauses. */
+ foreach(lc, rel-baserestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+ pull_varattnos((Node *) rinfo-clause, rel-relid, attrs_used);
+ }
+
+ /* Are any system columns requested from rel? */
scan_plan-fsSystemCol = false;
for (i = rel-min_attr; i 0; i++)
{
! if (bms_is_member(i - FirstLowInvalidHeapAttributeNumber, attrs_used))
{
scan_plan-fsSystemCol = true;
break;
}
}
+ bms_free(attrs_used);
+
return scan_plan;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 11:50 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: 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 we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Not all can be happy :) We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe functions, collections, without rewriting lot code. I remember lot of projects that promises compatibility with Oracle based on Firebird -- all are dead. Now situation is little bit different - there are big press for migration from Oracle, but Oracle is too big monster. Pavel Álvaro
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 a...@nosys.es mailto:a...@nosys.es: On 02/09/14 11:31, Pavel Stehule wrote: 2014-09-02 11:25 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es mailto:a...@nosys.es: 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 we'd choose a bizarre and quirky language instead of anything better just because it's standard. I'm sure current and prospective users will surely prefer a bizarre and quirky language that is standard approved, rather than a modern, comfortable, easy-to-use, that is not embodied by the ISO. No doubt ^_^ SQL/PSM is used in DB2, Sybase Anywhere, MySQL, That's a way better argument that it's standard :))) Still, I think postgres is in the position of attracting more Oracle than DB2+Sybase+MySQL users Not all can be happy :) We can implement SQL/PSM in conformity with ANSI SQL. But we cannot to implement PL/SQL be in 20% compatible with oracle - Aggegates, pipe functions, collections, without rewriting lot code. I remember lot of projects that promises compatibility with Oracle based on Firebird -- all are dead. Now situation is little bit different - there are big press for migration from Oracle, but Oracle is too big monster. OK. Thanks for all the info I was missing about this complexity, I see that it goes well beyond the syntax thing. However, I'd insist that this should be IMHO a big priority, and I'd set it as a long-term goal. Even better if it could have a phased approach, that would make a lot of people happier (targeting the most used functionality). I'm sure pushing us to implement those missing features would also be really good, too. In the meantime, having another language (probably not plpgsql2) that is modern and appealing to many users would be a very nice win. Regards, Álvaro
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 greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. Have you looked at http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already? As far as I can tell, that already *is* the case as far as the language goes. It seems to me that most of the stuff that's different between the two are things that are out of the control of the language (no autonomous transactions, function source code in a literal etc.) 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 brings a lot of users to postgres, and that helps us all. This would be a horrible, horrible lie. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. The best part about PL/PgSQL is the seamless integration with SQL. You can put an SQL expression pretty much anywhere. How well would that work if the framework was Javascript instead of the ADA-like body that both PL/SQL and PL/PgSQL implement? .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 more users than pg), that would benefit us all greatly. Even if not perfect. Even if it is a longer project which spans more than one release. But just having the syntax (or most of it, maybe avoiding some complex unimplemented postgres features, if that required a huge effort) is a big win. Have you looked at http://www.postgresql.org/docs/9.3/static/plpgsql-porting.html already? Precisely this page shows some indications of examples of things that could be done at a language level that would make it way easier to port from PL/SQL (if you don't use that unsupported stuff). At least for that, if the syntax is exactly the same, it could make things much more comfortable (I'm not aiming for a 0-effort port, at least in first place, but to get the 80% or 60% easier than now). As far as I can tell, that already *is* the case as far as the language goes. It seems to me that most of the stuff that's different between the two are things that are out of the control of the language (no autonomous transactions, function source code in a literal etc.) Maybe it would be interesting to analyze: - What it's impossible to have right now in postgres - What can be implemented in a different way, but that would work in postgres - What could be somehow emulated And adapt the syntax as much as possible to aim for the biggest compatibility possible. 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 brings a lot of users to postgres, and that helps us all. This would be a horrible, horrible lie. Certainly not more horrible than today's PostgreSQL has NoSQL. Despite that, I'm not saying I'd lie. I'd say what the media would say, which is completely different. If on the other hand we resign from attracting Oracle users, in a moment where non-Oracle databases are fighting for them. and we lose here well, let's at least have a very compelling, attractive, in-core, blessed, language. Even disliking it myself, PL/JavaScript would be my #1 candidate there. The best part about PL/PgSQL is the seamless integration with SQL. You can put an SQL expression pretty much anywhere. How well would that work if the framework was Javascript instead of the ADA-like body that both PL/SQL and PL/PgSQL implement? SQL integration is a must in a PL/* language, that's for sure. But leveraging a well known language, tooling, and, specially, external libraries/ecosystem is a much bigger win. Specially if all the languages that I know of are capable (with more or less effort) to integrate SQL. So maybe JavaScript with a way of integrating SQL would be preferable IMO. Regards, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 and...@2ndquadrant.com 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 :) Committed. Thanks for the patch! Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why after increase the hash table partitions, TPMC decrease
On Tue, Sep 2, 2014 at 2:09 PM, Xiaoyulei xiaoyu...@huawei.com 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 expect that after increase the number of partitions, reduces lock competition, TPMC should be increased. I think you can expect some increase mainly if your test is read only and you have sufficient RAM such that it can contain all the data, for other cases there can be I/O due to which you might not see any increase. But the test results on the contrary, after modified to 1024, TPMC did not increase, but decrease. Why such result? We modify the following macro definition: NUM_BUFFER_PARTITIONS 1024 LOG2_NUM_PREDICATELOCK_PARTITIONS 10 LOG2_NUM_LOCK_PARTITIONS 10 Increasing these numbers might lead to error too many LWLocks taken, unless you increase MAX_SIMUL_LWLOCKS. Once you can check the server log if it contains any errors, that might lead to decrease in performance. Also another side effect would be that increasing above numbers will lead to increase in shared memory usage. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
[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 收件人: Xiaoyulei 抄送: pgsql-hackers@postgresql.org 主题: Re: [HACKERS] why after increase the hash table partitions, TPMC decrease On Tue, Sep 2, 2014 at 2:09 PM, Xiaoyulei xiaoyu...@huawei.commailto:xiaoyu...@huawei.com 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 expect that after increase the number of partitions, reduces lock competition, TPMC should be increased. I think you can expect some increase mainly if your test is read only and you have sufficient RAM such that it can contain all the data, for other cases there can be I/O due to which you might not see any increase. But the test results on the contrary, after modified to 1024, TPMC did not increase, but decrease. Why such result? We modify the following macro definition: NUM_BUFFER_PARTITIONS 1024 LOG2_NUM_PREDICATELOCK_PARTITIONS 10 LOG2_NUM_LOCK_PARTITIONS 10 Increasing these numbers might lead to error too many LWLocks taken, unless you increase MAX_SIMUL_LWLOCKS. Once you can check the server log if it contains any errors, that might lead to decrease in performance. Also another side effect would be that increasing above numbers will lead to increase in shared memory usage. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.comhttp://www.enterprisedb.com/
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) it is not too much work, but I need a names for parameters I have tried adding dummy names (a, b, c) in pg_proc entry you have added. But that is not sufficient. We need to have default values provided to these arguments to work row_ro_json(x, ignore_null := true) call. It was not trivial. So I have not put much thought on that. For name, I choose (row, pretty, ignore_nulls) or similar. However it was my thought. If it is too complex of not so useful then we can ignore it. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
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 : HDD 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s, 10,000 rpm Benchmark: Scale : 16 Command :java JR /home/postgres/jdbcrunner-1.2/scripts/tpcc.js -sleepTime 550,250,250,200,200 Warmup time : 1 sec Measurement time : 900 sec Number of tx types : 5 Number of agents : 16 Connection pool size : 16 Statement cache size : 40 Auto commit : false Checkpoint segments:1024 Checkpoint timeout:5 mins Average % of CPU utilization at user level for multiple blocks compression: Compression Off = 3.34133 Snappy = 3.41044 LZ4 = 3.59556 Pglz = 3.66422 The numbers show the average CPU utilization is in the following order pglz LZ4 Snappy No compression Attached is the graph which gives plot of % CPU utilization versus time elapsed for each of the compression algorithms. Also, the overall CPU utilization during tests is very low i.e below 10% . CPU remained idle for large(~90) percentage of time. I will repeat the above tests with high load on CPU and using the benchmark given by Fujii-san and post the results. Thank you, On Wed, Aug 27, 2014 at 9:16 PM, Arthur Silva arthur...@gmail.com wrote: Em 26/08/2014 09:16, Fujii Masao masao.fu...@gmail.com escreveu: On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed rahilasye...@gmail.com wrote: Hello, Thank you for comments. Could you tell me where the patch for single block in one run is? Please find attached patch for single block compression in one run. Thanks! I ran the benchmark using pgbench and compared the results. I'd like to share the results. [RESULT] Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off202.0201.5 on6051.06053.0 pglz3543.03567.0 lz43344.03485.0 snappy3354.03449.5 Latency average during the benchmark. Unit is ms. MultipleSingle off19.119.0 on55.357.3 pglz45.045.9 lz444.244.7 snappy43.443.3 These results show that FPW compression is really helpful for decreasing the WAL volume and improving the performance. The compression ratio by lz4 or snappy is better than that by pglz. But it's difficult to conclude which lz4 or snappy is best, according to these results. ISTM that compression-of-multiple-pages-at-a-time approach can compress WAL more than compression-of-single-... does. [HOW TO BENCHMARK] Create pgbench database with scall factor 1000. Change the data type of the column filler on each pgbench table from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's gen_random_uuid() in order to avoid empty column, e.g., alter table pgbench_accounts alter column filler type text using gen_random_uuid()::text After creating the test database, run the pgbench as follows. The number of transactions executed during benchmark is almost same between each benchmark because -R option is used. pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared checkpoint_timeout is 5min, so it's expected that checkpoint was executed at least two times during the benchmark. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers It'd be interesting to check avg cpu usage as well. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com 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 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; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ I have no good ideas though on what the syntax would look like to allow zero rows or multiple rows for an UPDATE though. It's much harder to come up with things to *add* to a syntax than what obvious ugliness you want to *remove*. If I had to guess though, I would think something in the end of the UPDATE command like a new keyword, could work. It wouldn't mess up the syntax too much, and wouldn't require an extra line of code. I strongly feel we should give a plain UPDATE without any extra lines of code or special syntax a default behaviour, which is different from accept any number of affected rows. My definitive vote is to throw an error if not exactly 1 row was affected, and to provide a nice syntax to allow the other use cases. Right now it's the other way around, we never throw an error, and *always* have to check how many rows were affected. That means we *always* get both more lines of code and also uglier code in our applications, than we would if we optimized for the most common use case. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 make it shorter. The ForRedo is saying that pretty much. I committed the redo-routine refactoring patch. I kept the XLog prefix in the XLogReadBufferForRedo name; it's redundant, but all the other similar functions in xlogutils.c use the XLog prefix so it would seem inconsistent to not have it here. I'll post a new version of the main patch shortly... - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 pavel.steh...@gmail.com 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 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; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ 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? - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 14:16 GMT+02:00 Joel Jacobson j...@trustly.com: On Tue, Sep 2, 2014 at 11:04 AM, Pavel Stehule pavel.steh...@gmail.com 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 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; 3. What next? Maybe some notations - -- ** ensure_exact_one_row DELETE FROM tab WHERE xx = somevar; But default will be same as in plain SQL. All three suggestions are either too verbose, ugly or hackish. It is main problem for me. I am thinking so verbosity is important. If it is ugly, cannot to say. It is subjective. I write too much code every day in PL/pgSQL to find any other solution than the cleanest and simplest to be acceptable. I reckon there are those who mostly use the language to create aggregated reports or to run some kind of batch jobs. But I use it almost exlusively for OLTP, and then you most often update a single row, and if 0 or 1 rows are affected, it's an error. It is valid only for UPDATE, not for DELETE. You can delete with FK and it is common operation. Therefore, I wish the syntax for the most common use case to be as clean as possible, and there is nothing cleaner than plain UPDATE. Also, when showing a beginner the power of PL/pgSQL, it cannot be acceptable to have to write two rows to do something as simple as an update. All the suggestions above range between 2-3 rows (for DELETE, but I guess the syntax would be the same for UPDATE). For an in-depth discussion on this subject, please see http://joelonsql.com/2013/05/03/plpgsql-1-annoyance/ 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 I have no good ideas though on what the syntax would look like to allow zero rows or multiple rows for an UPDATE though. It's much harder to come up with things to *add* to a syntax than what obvious ugliness you want to *remove*. If I had to guess though, I would think something in the end of the UPDATE command like a new keyword, could work. It wouldn't mess up the syntax too much, and wouldn't require an extra line of code. I strongly feel we should give a plain UPDATE without any extra lines of code or special syntax a default behaviour, which is different from accept any number of affected rows. My definitive vote is to throw an error if not exactly 1 row was affected, and to provide a nice syntax to allow the other use cases. Right now it's the other way around, we never throw an error, and *always* have to check how many rows were affected. That means we *always* get both more lines of code and also uglier code in our applications, than we would if we optimized for the most common use case.
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/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 we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL
we turn it in a bizarre and quirky *non standard* language. Part of
the attraction of PL/pgsql is that it is Ada like - if we break that
too much then...well...that would be bad. So I think a careful
balance is needed, to add new features that keep the spirit of the
original language.
I agree. I think I haven't suggested adding new syntax to
pl/pgsql. But having its syntax similar to ADA is IMHO not something
good. I'm sure few prospective postgres users would be compelled to
that. They are compelled about JavaScript, python, Scala or Ruby, to
name a few, but definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably
the world's first programmer. If you're not familiar with modern Ada,
let me recommend the newly published Programming in Ada 2012 by John
Barnes. But I digress.
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 invoking SPI. OTOH, it actually relies on SPI under the hood
a lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a
GUC could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.postgresql.org/message-id/27477.1361916...@sss.pgh.pa.us I can't find Peter's email right now, but basically nobody liked the suggestion in the end. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 and...@dunslane.net wrote:
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/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 we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL we
turn it in a bizarre and quirky *non standard* language. Part of the
attraction of PL/pgsql is that it is Ada like - if we break that too much
then...well...that would be bad. So I think a careful balance is needed, to
add new features that keep the spirit of the original language.
I agree. I think I haven't suggested adding new syntax to pl/pgsql.
But having its syntax similar to ADA is IMHO not something good. I'm sure
few prospective postgres users would be compelled to that. They are
compelled about JavaScript, python, Scala or Ruby, to name a few, but
definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably the
world's first programmer. If you're not familiar with modern Ada, let me
recommend the newly published Programming in Ada 2012 by John Barnes. But
I digress.
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 invoking SPI. OTOH, it actually relies on SPI under the hood a
lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a GUC
could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 http://idallen.com/topposting.html Unfortunately, I think the short answer is not very well. In theory we cauld add in new types to a Javascript interpreter to handle them, but that would still leave you scrambling to handle user defined types. One of the advantages of plpgsql is that it can handle any Postgres data type without having to do anything special. The truth is that different PLs meet different needs and have different strengths and weaknesses. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 continue line and invert the condition, it works exactly the same, so it is just one useless instruction within that loop. From a logical point of view the loop is looking for '%' and then check whether the next char is '%' or not, so the straightforward code helps my understanding as it does exactly that, and the continue is just an hindrance to comprehension. Note that I would buy it if it helped avoid indenting further a significant portion of complex code, but this is not the case here. FWIW, I agree. [doc] I've incorporated these changes into this version of the patch, with small changes. Ok. With elog(ERROR, ..) it's still reported, but the user isn't fooled into thinking that the error is to be expected, and hopefully we would see a bug report. If it's impossible to tell the two errors apart, we might have subtly broken code carried around for who knows how long. Ok. In that case, it would make sense to keep distinct wordings of both exceptions in the execution code, so that they also can be set apart, i.e. keep the too many/few somewhere in the error? Well, you can do set log_error_verbosity='verbose' if you run into that. I think this patch has been thoroughly reviewed now. Committed, thanks! - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 14:38 GMT+02:00 Andrew Dunstan and...@dunslane.net:
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/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 we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL we
turn it in a bizarre and quirky *non standard* language. Part of the
attraction of PL/pgsql is that it is Ada like - if we break that too much
then...well...that would be bad. So I think a careful balance is needed, to
add new features that keep the spirit of the original language.
I agree. I think I haven't suggested adding new syntax to pl/pgsql.
But having its syntax similar to ADA is IMHO not something good. I'm sure
few prospective postgres users would be compelled to that. They are
compelled about JavaScript, python, Scala or Ruby, to name a few, but
definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably the
world's first programmer. If you're not familiar with modern Ada, let me
recommend the newly published Programming in Ada 2012 by John Barnes. But
I digress.
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 invoking SPI. OTOH, it actually relies on SPI under the hood a
lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
this test is unfair to plpgsql, and you know it well :)
any operations over native types will be faster than in plpgsql, although
this difference is maybe too much. Doesn't use --enable-cassert ?
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a GUC
could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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...@sss.pgh.pa.us). What happened to that proposal? http://www.postgresql.org/message-id/27477.1361916...@sss.pgh.pa.us I can't find Peter's email right now, but basically nobody liked the suggestion in the end. Perhaps we need the ONE ROW operatation ;) ONE ROW UPDATE ...; Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Joel Jacobson j...@trustly.com 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 if this has already been addressed -- but I wanted to register a strong objection to making this the default in any rewrite. If we want to support a setting or a statement option for it, fine; but in my personal experience in a production environment with thousands of plpgsql functions, most functions written to deal with one row at a time were orders of magnitude slower than they needed to be -- I spent a lot of my time rewriting them to use set logic so that they could benefit from the optimizer's attention. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. As a side note, of the many times I rewrote long functions which looped through individual rows, I would estimate that 80% of them had subtle bugs which were fixed by changing them to set logic. Sure, some of those would have caused run-time errors rather than plausible-but-incorrect results with the change you suggest, but far from all of them. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 invoking SPI. OTOH, it
actually relies on SPI under the hood a lot more that other PLs,
which I have little doubt is responsible for timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
this test is unfair to plpgsql, and you know it well :)
any operations over native types will be faster than in plpgsql,
although this difference is maybe too much. Doesn't use
--enable-cassert ?
It's not unfair, and no it isn't using cassert. This was from a
production grade server.
PLV8 has its own issues (see discussion elsewhere in this thread re
int64 and numeric). It's just that speed isn't one of them :-)
Please note that I'm not unhappy with plpgsql. I have my own small list
of things that I would like improved, but there isn't very much that
bugs me about it.
A few years ago I was largely instrumental in building an entire billing
system, including some very complex tax rating, for a small Telco, using
plpgsql plus a tiny bit of plperlu glue where we needed unsafe
operations. It was quite fast enough - see my talk at pgopen a few years
back.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner kgri...@ymail.com wrote: Joel Jacobson j...@trustly.com 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 if this has already been addressed -- but I wanted to register a strong objection to making this the default in any rewrite. If we want to support a setting or a statement option for it, fine; but in my personal experience in a production environment with thousands of plpgsql functions, most functions written to deal with one row at a time were orders of magnitude slower than they needed to be -- I spent a lot of my time rewriting them to use set logic so that they could benefit from the optimizer's attention. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. 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. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. I think the benefit of a secure and convenient way of updating exactly 1 row outweights the reduced convenience of updating multiple rows when you really want to update multiple rows. Compare this to the normal psql prompt. How many million dollars would you say the total cost would be for mistakes where someone forgets the WHERE-clause of an UPDATE or a DELETE? :-) It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes
On Tue, Sep 2, 2014 at 9:11 AM, Rahila Syed rahilasye...@gmail.com 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® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM: 32GB Disk : HDD 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s, 10,000 rpm Benchmark: Scale : 16 Command :java JR /home/postgres/jdbcrunner-1.2/scripts/tpcc.js -sleepTime 550,250,250,200,200 Warmup time : 1 sec Measurement time : 900 sec Number of tx types : 5 Number of agents : 16 Connection pool size : 16 Statement cache size : 40 Auto commit : false Checkpoint segments:1024 Checkpoint timeout:5 mins Average % of CPU utilization at user level for multiple blocks compression: Compression Off = 3.34133 Snappy = 3.41044 LZ4 = 3.59556 Pglz = 3.66422 The numbers show the average CPU utilization is in the following order pglz LZ4 Snappy No compression Attached is the graph which gives plot of % CPU utilization versus time elapsed for each of the compression algorithms. Also, the overall CPU utilization during tests is very low i.e below 10% . CPU remained idle for large(~90) percentage of time. I will repeat the above tests with high load on CPU and using the benchmark given by Fujii-san and post the results. Thank you, On Wed, Aug 27, 2014 at 9:16 PM, Arthur Silva arthur...@gmail.com wrote: Em 26/08/2014 09:16, Fujii Masao masao.fu...@gmail.com escreveu: On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed rahilasye...@gmail.com wrote: Hello, Thank you for comments. Could you tell me where the patch for single block in one run is? Please find attached patch for single block compression in one run. Thanks! I ran the benchmark using pgbench and compared the results. I'd like to share the results. [RESULT] Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off202.0201.5 on6051.06053.0 pglz3543.03567.0 lz43344.03485.0 snappy3354.03449.5 Latency average during the benchmark. Unit is ms. MultipleSingle off19.119.0 on55.357.3 pglz45.045.9 lz444.244.7 snappy43.443.3 These results show that FPW compression is really helpful for decreasing the WAL volume and improving the performance. The compression ratio by lz4 or snappy is better than that by pglz. But it's difficult to conclude which lz4 or snappy is best, according to these results. ISTM that compression-of-multiple-pages-at-a-time approach can compress WAL more than compression-of-single-... does. [HOW TO BENCHMARK] Create pgbench database with scall factor 1000. Change the data type of the column filler on each pgbench table from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's gen_random_uuid() in order to avoid empty column, e.g., alter table pgbench_accounts alter column filler type text using gen_random_uuid()::text After creating the test database, run the pgbench as follows. The number of transactions executed during benchmark is almost same between each benchmark because -R option is used. pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared checkpoint_timeout is 5min, so it's expected that checkpoint was executed at least two times during the benchmark. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers It'd be interesting to check avg cpu usage as well. Is there any reason to default to LZ4-HC? Shouldn't we try the default as well? LZ4-default is known for its near realtime speeds in exchange for a few % of compression, which sounds optimal for this use case. Also, we might want to compile these libraries with -O3 instead of the default -O2. They're finely tuned to work with all possible compiler optimizations w/ hints and other tricks, this is specially true for LZ4, not sure for snappy. In my virtual machine LZ4 w/ -O3 compression runs at twice the speed (950MB/s) of -O2 (450MB/s) @ (61.79%), LZ4-HC seems unaffected though (58MB/s) @ (60.27%). Yes, that's right, almost 1GB/s! And the compression ratio is only 1,5% short compared to LZ4-HC.
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 2:29 PM, Heikki Linnakangas hlinnakan...@vmware.com 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? From the STRICT mail thread, this was the last post: Marko Tiikkaja ma...@joh.to writes: If I'm counting correctly, we have four votes for this patch and two votes against it. Any other opinions? FWIW, I share Peter's poor opinion of this syntax. I can see the appeal of not having to write an explicit check of the rowcount afterwards, but that appeal is greatly weakened by the strange syntax. (IOW, if you were counting me as a + vote, that was only a vote for the concept --- on reflection I don't much like this implementation.) regards, tom lane 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 put it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 2:30 PM, Pavel Stehule pavel.steh...@gmail.com 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 throws an error otherwise? In what way could *not* changing the syntax of a standard UPDATE command, but changing the *behaviour*, in plpgsql2, be deemed to be a step in the COBOL direction? I don't want a new language, I love plpgsql, I just want to love it a bit more, I don't think I have to clarify on that any more. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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-zero value yields a zero. A zero rounded down from non-zero gets an error. I attached new patch. This includes a document about above behavior as Heikki suggested. regards, -- Tomonari Katsumata 2014-08-27 6:49 GMT+09:00 David G Johnston david.g.johns...@gmail.com: Tom Lane-2 wrote Robert Haas lt; robertmhaas@ gt; writes: I liked David Johnston's even stronger suggestion upthread: make it an error to specify a value requires rounding of any kind. In other words, if the minimum granularity is 1 minute, you can specify that as 60 seconds instead, but if you write 59 seconds, we error out. Maybe that seems pedantic, but I don't think users will much appreciate the discovery that 30 seconds means 60 seconds. They'll be happier to be told that up front than having to work it out afterward. I think this is totally wrong. The entire point of the GUC units system, or at least a large part of the point, is that users should not have to be intimately aware of the units in which a given value is measured internally. And that in turn means that the units had better be such that users won't find them overly coarse. If it matters a lot whether 59 seconds gets rounded to 60, then we didn't make a good choice of units for the GUC in question; and we should fix that choice, not mess with the rounding rules. The case where this argument falls down is for special values, such as where zero means something quite different from the smallest nonzero value. Peter suggested upthread that we should redefine any GUC values for which that is true, but (a) I think that loses on backwards compatibility grounds, and (b) ISTM zero is probably always special to some extent. A zero time delay for example is not likely to work. Maybe we should leave the rounding behavior alone (there's not much evidence that rounding in one direction is worse than another; although I'd also be okay with changing to round-to-nearest), and confine ourselves to throwing an error for the single case that an apparently nonzero input value is truncated/rounded to zero as a result of units conversion. To Andres' point: SELECT unit, count(*) FROM pg_settings WHERE unit '' GROUP BY unit; (9.3 / Ubuntu) min (1 - log_rotation_age) s (10) ms (13) kb (7) 8kb (6) I don't know about the size implications but they seem to be non-existent. That any setting critically matters at +/- 1s or 1ms doesn't seem likely in practice. Even +/- 1min for a setting, if it did matter at extreme scale, would be recognizable by the user in practice as a rounding artifact and compensated for. At this point throwing an error for any precision that results in less than the default precision is my preference. I would not change the rounding rules for the simple reason that there is no obvious improvement to be had and so why introduce pointless change that - however marginal and unlikely - will be user-visible. The complaint to overcome is avoiding an interpretation of zero when the precision of the input is less than the GUC unit. Lacking any concrete complaints about our round-down policy I don't see where a change there is worthwhile. Fixing zero as a special value falls under the same category. As mathematically pure as using infinity may be the trade-off for practicality and usability seems, even in light of this complaint, like the correct one to have made. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/proposal-rounding-up-time-value-less-than-its-unit-tp5811102p5816409.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers error_for_less-than_required_time-unit.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 rahilasye...@gmail.com 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® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM: 32GB Disk : HDD 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s, 10,000 rpm Benchmark: Scale : 16 Command :java JR /home/postgres/jdbcrunner-1.2/scripts/tpcc.js -sleepTime 550,250,250,200,200 Warmup time : 1 sec Measurement time : 900 sec Number of tx types : 5 Number of agents : 16 Connection pool size : 16 Statement cache size : 40 Auto commit : false Checkpoint segments:1024 Checkpoint timeout:5 mins Average % of CPU utilization at user level for multiple blocks compression: Compression Off = 3.34133 Snappy = 3.41044 LZ4 = 3.59556 Pglz = 3.66422 The numbers show the average CPU utilization is in the following order pglz LZ4 Snappy No compression Attached is the graph which gives plot of % CPU utilization versus time elapsed for each of the compression algorithms. Also, the overall CPU utilization during tests is very low i.e below 10% . CPU remained idle for large(~90) percentage of time. I will repeat the above tests with high load on CPU and using the benchmark given by Fujii-san and post the results. Thank you, On Wed, Aug 27, 2014 at 9:16 PM, Arthur Silva arthur...@gmail.com wrote: Em 26/08/2014 09:16, Fujii Masao masao.fu...@gmail.com escreveu: On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed rahilasye...@gmail.com wrote: Hello, Thank you for comments. Could you tell me where the patch for single block in one run is? Please find attached patch for single block compression in one run. Thanks! I ran the benchmark using pgbench and compared the results. I'd like to share the results. [RESULT] Amount of WAL generated during the benchmark. Unit is MB. MultipleSingle off202.0201.5 on6051.06053.0 pglz3543.03567.0 lz43344.03485.0 snappy3354.03449.5 Latency average during the benchmark. Unit is ms. MultipleSingle off19.119.0 on55.357.3 pglz45.045.9 lz444.244.7 snappy43.443.3 These results show that FPW compression is really helpful for decreasing the WAL volume and improving the performance. The compression ratio by lz4 or snappy is better than that by pglz. But it's difficult to conclude which lz4 or snappy is best, according to these results. ISTM that compression-of-multiple-pages-at-a-time approach can compress WAL more than compression-of-single-... does. [HOW TO BENCHMARK] Create pgbench database with scall factor 1000. Change the data type of the column filler on each pgbench table from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's gen_random_uuid() in order to avoid empty column, e.g., alter table pgbench_accounts alter column filler type text using gen_random_uuid()::text After creating the test database, run the pgbench as follows. The number of transactions executed during benchmark is almost same between each benchmark because -R option is used. pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared checkpoint_timeout is 5min, so it's expected that checkpoint was executed at least two times during the benchmark. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers It'd be interesting to check avg cpu usage as well. Is there any reason to default to LZ4-HC? Shouldn't we try the default as well? LZ4-default is known for its near realtime speeds in exchange for a few % of compression, which sounds optimal for this use case. Also, we might want to compile these libraries with -O3 instead of the default -O2. They're finely tuned to work with all possible compiler optimizations w/ hints and other tricks, this is specially true for LZ4, not sure for snappy. In my virtual machine LZ4 w/ -O3 compression runs at twice the speed (950MB/s) of -O2 (450MB/s) @ (61.79%), LZ4-HC seems unaffected though (58MB/s) @ (60.27%). Yes, that's right,
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 LZ4-HC is only slower to compress and the decompression is unaffected. This is about the write ahead log, not relations Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 down to the remote server or not. While this works fine, I wonder if it wouldn't be better to have some explicit flag in fdw_private for that purpose. Future modifications that change the list length might easily overlook that it is used for this purpose, thereby breaking the code. Other than that it looks alright to me. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 hlinnakan...@vmware.com 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? From the STRICT mail thread, this was the last post: Marko Tiikkaja ma...@joh.to writes: If I'm counting correctly, we have four votes for this patch and two votes against it. Any other opinions? FWIW, I share Peter's poor opinion of this syntax. I can see the appeal of not having to write an explicit check of the rowcount afterwards, but that appeal is greatly weakened by the strange syntax. (IOW, if you were counting me as a + vote, that was only a vote for the concept --- on reflection I don't much like this implementation.) regards, tom lane 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 put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 go into and I want to know which partitions I need to look for partition_key=Y. For the latter you really have need an expression per partition, or something equivalent. If performance is an issue I suppose you could live with having an old and an new partition scheme, so you couldn't have two live repartitionings happening simultaneously. Now, if you want to close the door on online repartitioning forever then that fine. But being in the position of having to say yes our partitioning scheme sucks, but we would have to take the database down for a week to fix it is no fun. Unless logical replication provides a way out maybe?? I am unclear why having information per-partition rather than on the parent table helps with online reparitioning. Robert's idea of using normal table inheritance means we can access/move the data independently of the partitioning system. My guess is that we will need to do repartitioning with some tool, rather than as part of normal database operation. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for psql History Display on MacOSX
Noah Misch n...@leadboat.com 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 latest patch revision changes \s /tmp/foo to match the novel output the patch introduces for plain \s. \s /tmp/foo would no longer write data that libedit can reload as a history file. BTW, I failed last night to produce a coherent argument against that particular point, but consider this. What are the main use-cases for \s to a file? I argue that they are 1. Create a human-readable record of what you did. 2. Create the starting point for a SQL script file. I do not deny it's possible that somebody out there is also using \s for 3. Create a file that I can overwrite ~/.psql_history with later. But if this is being done in the field at all, surely it is miles behind the applications listed above. Now, if you are using libreadline, the output of \s has always been perfectly fit for purposes 1 and 2, because it's plain text of the history entries. Moreover, it is *not* particularly fit for purpose 3, because intra-command newlines aren't encoded. Yes, you could get libreadline to read the file, but multiline SQL commands will be seen as multiple history entries which is very far from convenient to use. (This adds to my suspicion that nobody is doing #3 in practice.) On the other hand, if you are using libedit, purpose 3 works great but the output is utterly unfit for either purpose 1 or 2. Here are the first few lines of ~/.psql_history on one of my Macs: _HiStOrY_V2_ explain\040verbose\^A\040\040select\0401\^Aunion\^A\040\040select\0402; \\q select\0404; explain\040verbose\^A\040\040select\0401\^Aunion\^A\040\040select\0402; select\04044; \\q \\s \\s\040foobar \\q What the proposed patch does is ensure that \s produces plain text regardless of which history library you are using. I think arguing that we shouldn't do that is stretching the concept of backwards compatibility well past the breaking point. Moreover, output like the above doesn't satisfy the existing description of \s, namely that it prints your history. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas hlinnakan...@vmware.com 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 put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 hlinnakan...@vmware.com 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 put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? Well, I'm in the camp that the current default is fine... - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 hlinnakan...@vmware.com 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 put it. Then you'll have to argue what the *other* syntax should look like. And not everyone agrees on the default either, see Kevin's email. Designing a new language is going to be an uphill battle, even more so than enhancing current plpgsql. Any ideas on what the *other* syntax could look like? 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_COUNT) which can be consulted after the operation. For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows IF row_count 1 THEN RAISE EXCEPTION 'oh no'; END IF; This, obviously, requires us to get rid of the requirement for PERFORM today, which I see as a win as well. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER SYSTEM RESET?
Fujii Masao wrote: On Mon, Sep 1, 2014 at 10:54 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao masao.fu...@gmail.com wrote: The patch looks good to me. One minor comment is; probably you need to update the tab-completion code. Thanks for the review. I have updated the patch to support tab-completion. As this is a relatively minor change, I will mark it as Ready For Committer rather than Needs Review. Thanks for updating the patch! One more minor comment is; what about applying the following change for the tab-completion for RESET ALL? This causes the tab-completion of even ALTER SYSTEM SET to display all and that's strange. But the tab-completion of SET has already had the same problem. So I think that we can live with that. Right and I have checked that behaviour is same for other similar statements like Alter Database database_name SET config_var or Alter User user_name SET config_var. So, the change made by you is on similar lines. OK. Applied. Uhm, are we agreed on the decision on not to backpatch this? I would think this should have been part of the initial ALTER SYSTEM SET patch and thus should be backpatched to 9.4. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 3:58 PM, Marko Tiikkaja ma...@joh.to 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_COUNT) which can be consulted after the operation. For example: UPDATE foo WHERE bar = 1; -- must affect exactly one row PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows IF row_count 1 THEN RAISE EXCEPTION 'oh no'; END IF; This, obviously, requires us to get rid of the requirement for PERFORM today, which I see as a win as well. I don't like rebranding the PERFORM command, as that would require all existing code with PERFORM commands to be changed. That also still requires 4 rows for some all other use-cases than 1 row affected, if all you want is a general error in case your expectations of rows affected were not met. I think with a single line of UPDATE command, you should be forced to indicate you want something else than 1 row affected, and if your expectations are not met, you should get the error on the UPDATE command, not having to check a variable on the next line of code. I therefore think, since we don't have to be 100% backwards compatible, it's OK and a good thing to introduce some new keyword to UPDATE (and DELETE). I have no ideas on what keyword(s) though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Marko Tiikkaja ma...@joh.to 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. If you want a restriction to exactly one row, that needs to be a modifier. I take no position on how the modifier should be spelled, though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 amit.kapil...@gmail.com wrote: On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao masao.fu...@gmail.com wrote: The patch looks good to me. One minor comment is; probably you need to update the tab-completion code. Thanks for the review. I have updated the patch to support tab-completion. As this is a relatively minor change, I will mark it as Ready For Committer rather than Needs Review. Thanks for updating the patch! One more minor comment is; what about applying the following change for the tab-completion for RESET ALL? This causes the tab-completion of even ALTER SYSTEM SET to display all and that's strange. But the tab-completion of SET has already had the same problem. So I think that we can live with that. Right and I have checked that behaviour is same for other similar statements like Alter Database database_name SET config_var or Alter User user_name SET config_var. So, the change made by you is on similar lines. OK. Applied. Uhm, are we agreed on the decision on not to backpatch this? I would think this should have been part of the initial ALTER SYSTEM SET patch and thus should be backpatched to 9.4. I think it belongs in 9.4 as well, especially if we're having another beta. -- Vik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 2014-09-02 10:21:50 -0400, Tom Lane wrote: Marko Tiikkaja ma...@joh.to 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. If you want a restriction to exactly one row, that needs to be a modifier. I take no position on how the modifier should be spelled, though. Personally I think ONE ROW UPDATE ... reads nicely and SQL-ish. But it's not very expandable to other numbers. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Joel Jacobson j...@trustly.com wrote: On Tue, Sep 2, 2014 at 3:12 PM, Kevin Grittner kgri...@ymail.com wrote: Joel Jacobson j...@trustly.com 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. Getting people to write things in a declarative style in the first place was difficult because so many of the programmers were so attached to the imperative style of coding; making it more difficult for people to Do The Right Thing is a bad idea IMO. 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 value can't have more than one matching row. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. I think the benefit of a secure and convenient way of updating exactly 1 row outweights the reduced convenience of updating multiple rows when you really want to update multiple rows. I don't. Compare this to the normal psql prompt. How many million dollars would you say the total cost would be for mistakes where someone forgets the WHERE-clause of an UPDATE or a DELETE? :-) Dunno, but that also tends to suggest a solution that isn't limited to a PL would be beneficial. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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's no need to break all the current uses of PERFORM. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com 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 value can't have more than one matching row. No, but your code can have a bug. INTO rejecting any queries returning more than one row helps, though, but having to write RETURNING TRUE INTO _OK; is not pretty either. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. Updating even two rows instead of one can have catastrophic effects. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). Yeah, it doesn't necessarily need to be the default default (and I see a lot of people saying it shouldn't be). Even having a per-query modifier would be better than the current behaviour. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: 答复: [HACKERS] why after increase the hash table partitions, TPMC decrease
On Tue, Sep 2, 2014 at 5:20 PM, Xiaoyulei xiaoyu...@huawei.com 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 which means it contains mix of reads and writes, now I am not sure how you have identified that increasing buffer partitions can improve the performance. Have you used any profiling? So I think maybe pg is blocked by some place in itself. Yeah, there's another lock BufFreelistLock which is a major cause of contention in buffer allocation and for which already work is in progress for 9.5. However as mentioned previously, that will be useful mainly for Read only loads. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] delta relations in AFTER triggers
On Mon, Sep 1, 2014 at 9:09 PM, Tom Lane t...@sss.pgh.pa.us 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 different semantics that require effort to reason about. Variables and function arguments are non-contriversial, every experienced coder understands their semantics without thinking twice -- even if they're not familiar with Postgres. The concept of lightweight relations that pop into existence when a certain kind of trigger definition is used somewhere in the function stack, without a CREATE TABLE, without being discoverable in information_schema etc., I find needs some more justification than I've seen in this thread. So far I've only heard that it's more convenient to implement in the current PostgreSQL code base. I'm sure more questions would pop up in practice, but as Heikki mentioned: Are such relations also visible to other functions called by the trigger function? * If yes, this introduces non-obvious dependencies between functions. What happens when one trigger with delta relations invokes another trigger, does the previous one get shadowed or overwritten? What are the interactions with search_path? Can an unprivileged function override relation names when calling a SECURITY DEFINER function? * If not, this further inhibits developers from properly modularizing their trigger code (this is already a problem due to the current magic trigger variables). Even if these questions have reasonable answers, it takes mental effort to remember the details. Procedure code debugging, especially triggers, is hard enough due to poor tooling; increasing the cognitive load should not be done lightly. You could argue that CREATE TEMP TABLE already has some of these problems, but it's very rare that people actually need to use that. If delta relations get built on this new mechanism, avoiding won't be an option any more. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote: 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 brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Have you ever tried any of the real NoSQL products version of infinite scalability ? We are no worse than most if you use just the unstructured part (which is what the NoSQL crowd provides) and something like pl/proxy for scaling. Cheers -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ
Re: [HACKERS] postgresql latency bgwriter not doing its job
On Tue, Aug 26, 2014 at 1:02 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello again, I have not found any mean to force bgwriter to send writes when it can. (Well, I have: create a process which sends CHECKPOINT every 0.2 seconds... it works more or less, but this is not my point:-) There is scan_whole_pool_milliseconds, which currently forces bgwriter to circle the buffer pool at least once every 2 minutes. It is currently fixed, but it should be trivial to turn it into an experimental guc that you could use to test your hypothesis. I recompiled with the variable coldly set to 1000 instead of 12. The situation is slightly degraded (15% of transactions were above 200 ms late). However it seems that bgwriter did not write much more pages: You should probably try it set to 200 rather than 1000, to put it on an equal footing with the checkpoint_timeout of 0.2 seconds you reported on. Not that I think this will improve the situation. Afterall, my theory is that it does not matter who *writes* the pages, it only matters how they get fsynced. buffers_checkpoint = 26065 buffers_clean = 5263 buffers_backend = 367 Or I may have a problem interpreting pg_stat_bgwriter. For this experiment, what was checkpoint_timeout set to? Cheers, Jeff
Re: [HACKERS] PL/pgSQL 2
Marko Tiikkaja ma...@joh.to wrote: On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com 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 value can't have more than one matching row. No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. INTO rejecting any queries returning more than one row helps, though, but having to write RETURNING TRUE INTO _OK; is not pretty either. No, that sure would not be. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. By the time you find out that the number of rows affected is every row in the table, you have horribly bloated the table and all its indexes. Causing a DML statement to abort when it sees a second row is a completely different issue than what I (and I suspect most others on the list) thought we were talking about, and would need to affect far more than the PL. Updating even two rows instead of one can have catastrophic effects. That's a different problem than Joel just said was his main concern. I was pointing out that the solution he was proposing was a very poor solution to the problem he said he was trying to solve. Can you imagine the damage if a function that updated every row in a table whenever anyone tried to update a single row by primary key made it past testing and staging phases into production? Depending on the table, it might not need to run more than a few times before the bloat ate all disk space and your production environment was totally hosed to the point of needing to delete everything from $PGDATA and restore from your last known good backup. Accidentally updating a single unintended row is a whole different class of problem, with potentially completely different solutions. We can talk about both, but let's not conflate them. The proposed new behavior seems like it would only detect a small percentage of ways you can accidentally update unintended rows, but I agree it would catch enough of them to be a potentially useful option. If it were a new option on the DML statement syntax, once could certainly have code review or some sort of lint software to look for omissions. If you don't have a code review process before things hit production, well, mechanical solutions like this can only be expected to catch a small percentage of the damage from application bugs deployed to production. It's the same type of mistake I want to prevent from in a convenient way, and there is nothing more convenient than the default behavour. That also means *all* users will get that behaviour even if they don't explicitly request it, which is a good thing, because then they are protected against the danger of not knowing how to make sure it updated/deleted only one row. I think that changing the default behavior of SQL from set oriented to something else is a horrible idea. I absolutely, unequivocally oppose that at the SQL or plpgsql level as harmful. I understand the need to check for this in various cases, and in fact the application framework I designed at my previous job had Java methods for doing DML with such a check included, named InsertOneRow(), UpdateOneRow(), and DeleteOneRow(). Very useful. If we can agree on a way to allow users to do the same in plpgsql, fine -- but certainly not as the default default (word intentionally repeated). Yeah, it doesn't necessarily need to be the default default (and I see a lot of people saying it shouldn't be). Even having a per-query modifier would be better than the current behaviour. There we seem to agree. I definitely think it is a useful option if we can sort out a good way to allow it. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Sep 1, 2014, at 10:24 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 09/02/2014 08:09 AM, Neil Tiffin wrote: Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL. PL/Perl is plenty fast, FWIW. Good to know. I used to do a lot of perl and will revisit the language. I agree that it is unfortunate that we don't have an in-core trusted real language PL other than PL/Perl. I am personally hoping that PL/V8 will be in a position to be adopted as PL/JavaScript soon, as that would be an excellent fit with how the language fashion world is currently moving - JSON and JavaScript abound. More seriously, JavaScript is also a good fit for a trusted PL. I've long favoured Lua because of the excellent embeddable runtime and security-friendly design, but it's never really got the uptake required to make it a serious contender. I'd be quite happy to see PL/JavaScript in-core. (The other obvious candidate would be PL/Ruby, but it doesn't have an untrusted variant, and AFAIK Ruby is no better than Python when it comes to supporting a secure runtime: hopeless.) That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. 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. I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6 or 10 level quotes to make it work. So instead of (from the docs, 40.6.4. Looping Through Query Results) EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name); EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query; should be something like: EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’; EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’; Wow, so after I wrote the above, I went back to review the docs and lo and behold the format function was added in 9.1 (I think). It turns out it can already be written as (not tested) EXECUTE format( ‘TRUNCATE TABLE %I’, mviews.mv_name); EXECUTE format( ‘INSERT INTO %I %L’, mviews.mv_name, mviews.mv_query); That’s not so bad and very similar to how it would have to be done in many other languages. However the first three examples in the docs for PL/pgSQL for dynamic queries and many, many other places don’t show this approach. And the format syntax is only listed 4 lines from the bottom of the section as a ‘you can also do this’. From the position and wording I would interpret that something must be wrong with using the format function to construct dynamic queries, but, who knew, I never scrolled down that far in the docs. Thank you to whomever added the format() function. So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended? And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks) Neil -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql latency bgwriter not doing its job
There is scan_whole_pool_milliseconds, which currently forces bgwriter to circle the buffer pool at least once every 2 minutes. It is currently fixed, but it should be trivial to turn it into an experimental guc that you could use to test your hypothesis. I recompiled with the variable coldly set to 1000 instead of 12. The situation is slightly degraded (15% of transactions were above 200 ms late). However it seems that bgwriter did not write much more pages: You should probably try it set to 200 rather than 1000, to put it on an equal footing with the checkpoint_timeout of 0.2 seconds you reported on. As I understand it, the setting makes the bgwriter processe scan all shared_buffers every this amount of time... but ITSM that the key point is that bgwriter has no insentive to start writing out buffers anyway with its current decision rules, and that should not change with the frequency at which they are scanned (?) For this experiment, what was checkpoint_timeout set to? AFAICR, the default, 5min. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER SYSTEM RESET?
Re: Vik Fearing 2014-09-02 5405d2d9.9050...@dalibo.com Uhm, are we agreed on the decision on not to backpatch this? I would think this should have been part of the initial ALTER SYSTEM SET patch and thus should be backpatched to 9.4. I think it belongs in 9.4 as well, especially if we're having another beta. My original complaint was about 9.4, so I'd like to see it there, yes. IMHO it doesn't make sense to ship a crippled version first, let users get used to the fact that (RE)SET and ALTER SYSTEM (RE)SET behave differently, and then ship the full feature in 9.5 later. Also, this should be something that is trivially to test, so there's little chance of slipping bugs into 9.4 that would go unnoticed. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 9/2/14 5:08 PM, Kevin Grittner wrote: Marko Tiikkaja ma...@joh.to wrote: On 9/2/14 4:26 PM, Kevin Grittner wrote: Joel Jacobson j...@trustly.com 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 value can't have more than one matching row. No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? The main use case is making it more clear *during testing* that the code is broken. It doesn't hurt that it would also not trash your data if someone deployed bad code into production, but I think it's more important to have good tools for testing your code. I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Perhaps. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql latency bgwriter not doing its job
On Tue, Sep 2, 2014 at 8:14 AM, Fabien COELHO coe...@cri.ensmp.fr wrote: There is scan_whole_pool_milliseconds, which currently forces bgwriter to circle the buffer pool at least once every 2 minutes. It is currently fixed, but it should be trivial to turn it into an experimental guc that you could use to test your hypothesis. I recompiled with the variable coldly set to 1000 instead of 12. The situation is slightly degraded (15% of transactions were above 200 ms late). However it seems that bgwriter did not write much more pages: You should probably try it set to 200 rather than 1000, to put it on an equal footing with the checkpoint_timeout of 0.2 seconds you reported on. As I understand it, the setting makes the bgwriter processe scan all shared_buffers every this amount of time... but ITSM that the key point is that bgwriter has no insentive to start writing out buffers anyway with its current decision rules, and that should not change with the frequency at which they are scanned (?) Ah, I see now. The usage counts are not zero, so it visits the buffer and then leaves it alone. Cheers, Jeff
Re: [HACKERS] COPY and heap_sync
On Sun, Aug 31, 2014 at 6:10 AM, Peter Eisentraut pete...@gmx.net wrote: On 8/30/14 2:26 AM, Jeff Janes wrote: But there cases were people use COPY in a loop with a small amount of data in each statement. What would be the reason for doing that? As far as I can tell, DRY. They need code to do bulk inserts anyway. So, just use that everywhere even when it is not in bulk. Also, you can't interleave a copy command with other queries on the same connection. So you code it to start a COPY, use it until you discover you need to run a query (because you encounter something not in you local cache), end the COPY and do that query, then restart the query. Under some conditions, the interruption occurs very seldom, under other conditions it is pretty much every row. Cheers, Jeff
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner kgri...@ymail.com wrote: Marko Tiikkaja ma...@joh.to wrote: No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected. Updating even two rows instead of one can have catastrophic effects. That's a different problem than Joel just said was his main concern. I was pointing out that the solution he was proposing was a very poor solution to the problem he said he was trying to solve. Can you imagine the damage if a function that updated every row in a table whenever anyone tried to update a single row by primary key made it past testing and staging phases into production? Depending on the table, it might not need to run more than a few times before the bloat ate all disk space and your production environment was totally hosed to the point of needing to delete everything from $PGDATA and restore from your last known good backup. Sorry for being unclear, I didn't mean to suggest the main concern is updating *all* rows. The main concern is when you have a rather complex UPDATE WHERE clause, aiming to update exactly one row. Some of the expressions might be assertions, to just double-verify the values and to make it stand-out you are checking those expressions. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 11:10 PM, Neil Tiffin wrote: I’d be happy with PL/Javascript, PL/Lua or ?? as long as creating dynamic SQL queries was simple, i.e. no goofball 6 or 10 level quotes to make it work. So instead of (from the docs, 40.6.4. Looping Through Query Results) EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name); EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query; should be something like: EXECUTE ‘TRUNCATE TABLE $$mviews.mv_name’; EXECUTE ‘INSERT INTO $$mviews.mv_name $$mviews.mv_query’; I think we need to remove those sections entirely from the docs, in favour of using only format(...) with EXECUTE ... USING . Too many people seem to see that, and not format(...). So what’s wrong with using format() for dynamic queries and why is the approach not more prominent or recommended? Historical, really. And the format function option is not even listed in the section on quoting (40.11.1. Handling of Quotation Marks) That's a real oversight that needs fixing. Thanks. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 06:44 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner kgri...@ymail.com wrote: Marko Tiikkaja ma...@joh.to wrote: No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected. Marko posted a patch to add assertions to PL/pgSQL last year, see http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a long thread, but in the end I think everyone was more or less OK with the syntax ASSERT condition;. I also think that syntax is fine, and it would be a nice feature, assuming we can avoid reserving the ASSERT keyword. I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
Joel Jacobson j...@trustly.com 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. If the where-clause would be incorrect and the update would update all rows in the table, that would be a disaster, which is what I want to prevent. Joel Jacobson j...@trustly.com wrote: Sorry for being unclear, I didn't mean to suggest the main concern is updating *all* rows. The main concern is when you have a rather complex UPDATE WHERE clause, aiming to update exactly one row. Some of the expressions might be assertions, to just double-verify the values and to make it stand-out you are checking those expressions. These are two different problems which probably need two different solutions. Making the default behavior of a set-based command that it throw an error if the resulting set is not exactly one row doesn't seem like the right solution to either one of them. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. So instead of one line of code, I would need to write two lines of code at almost *all* places where a currently have an UPDATE. :-( In that case, I think RETURNING TRUE INTO STRICT _OK is less ugly. I think the problem with my perspective is my ambitions. I use PL/pgSQL not as a secondary language, but it's my primary language for developing applications. For me, updating a row, is like setting a variable in a normal language. No normal language would require two rows to set a variable. It would be like having to do: my $var = 10; die unless $var == 10; in Perl to set a variable. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 17:03, Hannu Krosing wrote: On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote: 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 brings a lot of users to postgres, and that helps us all. Partial true can enforce so lot of people will hate postgres too. False promises are wrong Then let's stop talking about postgres being NoSQL. NoSQL is basically schema-less (really bad name) plus infinite scalability (which basically means transparent sharding). We fail to provide the latter very clearly... Have you ever tried any of the real NoSQL products version of infinite scalability ? Yes, and they are absolutely not infinite, and they suck in many other places. But they scale beyond one node, transparently, something that postgres doesn't. And regardless, this is what people is buying, we like it or not. We are no worse than most if you use just the unstructured part (which is what the NoSQL crowd provides) and something like pl/proxy for scaling. We are definitely worse. This is the problem, we only look to our own belly bottom (if this expression exists in English). All NoSQL scale *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying they don't suck at many many other things, or that some of them may be worse solution than the problem. But despite JSON/JSONB in pg is awesome, it's far far away from what we need to compete agains NoSQL in these regards. Ask anyone not in the postgres world to use pl/proxy for scaling and they will run away to mongo/whatever. Talk about HA... and the discussion is over :( I know how hard these problems are in the general, transactional approach that postgres takes, and that NoSQL does this for very simple, non-ACID cases, but they do. Hence, we cannot claim NoSQL compliance, just because we have jsonb. Unfortunately :( (Surely we do have many other values, but let's not say that we have NoSQL capabilities, because we don't while others -better or worse- do). Regards, Álvaro
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner kgri...@ymail.com wrote: Joel Jacobson j...@trustly.com wrote: Sorry for being unclear, I didn't mean to suggest the main concern is updating *all* rows. The main concern is when you have a rather complex UPDATE WHERE clause, aiming to update exactly one row. Some of the expressions might be assertions, to just double-verify the values and to make it stand-out you are checking those expressions. These are two different problems which probably need two different solutions. Making the default behavior of a set-based command that it throw an error if the resulting set is not exactly one row doesn't seem like the right solution to either one of them. I see your point. Basically, we have two types of applications where PL/pgSQL is commonly used. a) OLTP applications where you typically operate on one row for each UPDATE command. b) Data warehouseing applications where you process multiple rows in each UPDATE command. Both have different desired default behaviours of the different set-based commands used in PL/pgSQL. I think both are important enough to motivate a nice syntax for both use-cases. If we cannot change the default behaviour of UPDATE, then I vote for the eariler proposed STRICT UPDATE syntax. That would not protect novice users (like myself a couple of years ago) who falsly thinks an UPDATE which updated 0 rows would fail. But at least it would provide them a quite nice syntax to fix that when shit hits the fan due to their failure. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 12:12 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. So instead of one line of code, I would need to write two lines of code at almost *all* places where a currently have an UPDATE. :-( In that case, I think RETURNING TRUE INTO STRICT _OK is less ugly. I think the problem with my perspective is my ambitions. I use PL/pgSQL not as a secondary language, but it's my primary language for developing applications. For me, updating a row, is like setting a variable in a normal language. No normal language would require two rows to set a variable. It would be like having to do: my $var = 10; die unless $var == 10; in Perl to set a variable. That's really a problem with your perspective. UPDATE is inherently set oriented. It's emphatically NOT like setting a single variable. I must have written tens, possibly hundreds of thousands of lines of plpgsql, and this have never ever been a problem for me. I'd be very opposed to adding some special new plpgsql-only syntax to have UPDATE or DELETE error out if they affected more than a single row. And as you and others have observed, you can do that now with the RETURNING true INTO STRICT ok trick. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: We are definitely worse. This is the problem, we only look to our own belly bottom (if this expression exists in English). All NoSQL scale *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying they don't suck at many many other things, or that some of them may be worse solution than the problem. But despite JSON/JSONB in pg is awesome, it's far far away from what we need to compete agains NoSQL in these regards. So the discussion started out with a desire to improve PL/pgSQL. Now somehow NoSQL and JSON is discussed in the same thread. Interesting. Godwin's Law never fails :-) http://en.wikipedia.org/wiki/Godwin's_law -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-02 18:03 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com: On 09/02/2014 06:44 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner kgri...@ymail.com wrote: Marko Tiikkaja ma...@joh.to wrote: No, but your code can have a bug. So the main use case is to allow buggy functions which are deployed to production without adequate testing to be detected? Bugs like not getting the primary key column(s) right? I think it would be great to have some way to generate an error if a given statement doesn't affect exactly one row, but the above is a pretty weak argument for making it a default behavior. Instead of writing unit tests for such trivial things as updating one row and testing if it got updated, it's better to make such unit tests asserts instead, which is exactly what we achieve if we provide a syntax to throw an error if not exactly 1 row was affected. Marko posted a patch to add assertions to PL/pgSQL last year, see http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a long thread, but in the end I think everyone was more or less OK with the syntax ASSERT condition;. I also think that syntax is fine, and it would be a nice feature, assuming we can avoid reserving the ASSERT keyword. I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. I like it Regards Pavel - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 18:20, Joel Jacobson wrote:
On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner kgri...@ymail.com wrote:
Joel Jacobson j...@trustly.com wrote:
Sorry for being unclear, I didn't mean to suggest the main concern is
updating *all* rows.
The main concern is when you have a rather complex UPDATE WHERE clause,
aiming to update exactly one row. Some of the expressions might be
assertions, to just double-verify the values and to make it stand-out
you are checking those expressions.
These are two different problems which probably need two different
solutions. Making the default behavior of a set-based command that
it throw an error if the resulting set is not exactly one row
doesn't seem like the right solution to either one of them.
I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.
b) Data warehouseing applications where you process multiple rows in
each UPDATE command.
Both have different desired default behaviours of the different
set-based commands used in PL/pgSQL.
I think both are important enough to motivate a nice syntax for both use-cases.
If we cannot change the default behaviour of UPDATE, then I vote for
the eariler proposed STRICT UPDATE syntax.
I see both use cases, but I think the SQL default are set
operations. For this particular point, if there would be just a syntax
change rather than a new language, in the line of the ONE ROW UPDATE
syntax, I'd say UNIQUE UPDATE. This {ONE ROW, UNIQUE} syntax may also
enforce having a WHERE clause. I find both better than the STRICT, which
is not doing what -you may think- it does.
Regards,
Álvaro
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 07:12 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. So instead of one line of code, I would need to write two lines of code at almost *all* places where a currently have an UPDATE. :-( Right. Doesn't really seem that bad, to be honest. You can put it on the same line if you wish. I think the problem with my perspective is my ambitions. I use PL/pgSQL not as a secondary language, but it's my primary language for developing applications. Sure, a lot of people do that. For me, updating a row, is like setting a variable in a normal language. No normal language would require two rows to set a variable. It would be like having to do: my $var = 10; die unless $var == 10; in Perl to set a variable. I don't think most applications are like that. See Kevin's comments about doing things in a set-oriented way instead of row-by-row. I know I've changed several procedures from the row-oriented style, looping over rows with a FOR loop, updating each one individually, to set-oriented style with a single UPDATE for a bunch of rows. It makes for more concise code, and performs better. I'm sure there are counter-examples, and I've also written many UPDATE statements that are expected to update exactly one row, but I find an ASSERT would be adequate for that. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 06:27 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: We are definitely worse. This is the problem, we only look to our own belly bottom (if this expression exists in English). All NoSQL scale *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying they don't suck at many many other things, or that some of them may be worse solution than the problem. But despite JSON/JSONB in pg is awesome, it's far far away from what we need to compete agains NoSQL in these regards. So the discussion started out with a desire to improve PL/pgSQL. Now somehow NoSQL and JSON is discussed in the same thread. Interesting. Godwin's Law never fails :-) http://en.wikipedia.org/wiki/Godwin's_law Not to mention completely unsubstantiated claims about *all* NoSQL scaling *easily* and *transparently* beyond one node :) -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 02/09/14 18:33, Hannu Krosing wrote: On 09/02/2014 06:27 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: We are definitely worse. This is the problem, we only look to our own belly bottom (if this expression exists in English). All NoSQL scale *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying they don't suck at many many other things, or that some of them may be worse solution than the problem. But despite JSON/JSONB in pg is awesome, it's far far away from what we need to compete agains NoSQL in these regards. So the discussion started out with a desire to improve PL/pgSQL. Now somehow NoSQL and JSON is discussed in the same thread. Interesting. Godwin's Law never fails :-) http://en.wikipedia.org/wiki/Godwin's_law Not to mention completely unsubstantiated claims about *all* NoSQL scaling *easily* and *transparently* beyond one node :) Honestly, this is off-topic and we can argue forever, but regardless all do or not, what's sure is that Postgres doesn't have horizontal scalability. Period. And this is what we should look at. And we can't claim we're NoSQL until we have (easy, transparent) horizontal scalability. Best, Álvaro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 04:24:11PM +0200, Andres Freund wrote: On 2014-09-02 10:21:50 -0400, Tom Lane wrote: Marko Tiikkaja ma...@joh.to 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. If you want a restriction to exactly one row, that needs to be a modifier. I take no position on how the modifier should be spelled, though. Personally I think ONE ROW UPDATE ... reads nicely and SQL-ish. But it's not very expandable to other numbers. SINGLETON UPDATE ...? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 12:40:14AM -0400, Tom Lane wrote: Craig Ringer cr...@2ndquadrant.com 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 looking to save a few hundred grand on per-CPU licensing. As a case in point, EDB have spent quite a few man-years on their Oracle compatibility layer; and it's still not a terribly exact match, according to my colleagues who have looked at it. So that is a tarbaby I don't personally care to touch ... even ignoring the fact that cutting off EDB's air supply wouldn't be a good thing for the community to do. FYI, the docs of what EDB has done are online: Server: http://www.enterprisedb.com/docs/en/9.3/eeguide/Table%2520of%2520Contents.htm Server packages, e.g. DBMS_: http://www.enterprisedb.com/docs/en/9.3/eeguide/Postgres_Plus_Enterprise_Edition_Guide-52.htm#P14240_790554 Oracle Compatibility Guide: http://www.enterprisedb.com/docs/en/9.3/oracompat/Table%2520of%2520Contents.htm PL/SQL, called Stored Procedure Language: http://www.enterprisedb.com/docs/en/9.3/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-78.htm#P6933_375311 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 9/2/14 6:03 PM, Heikki Linnakangas wrote: Marko posted a patch to add assertions to PL/pgSQL last year, see http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a long thread, but in the end I think everyone was more or less OK with the syntax ASSERT condition;. I also think that syntax is fine, and it would be a nice feature, assuming we can avoid reserving the ASSERT keyword. Did you really mean to say more or less OK? I didn't wade through the thread, but my recollection is that I was the only one truly OK with it, some people expressed concerns but appeared undecided, and the rest of the participants were completely against it. I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. I agree with Joel here; I think a shorter syntax is necessary. .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 6:31 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I don't think most applications are like that. See Kevin's comments about doing things in a set-oriented way instead of row-by-row. I know I've changed several procedures from the row-oriented style, looping over rows with a FOR loop, updating each one individually, to set-oriented style with a single UPDATE for a bunch of rows. It makes for more concise code, and performs better. I'm sure there are counter-examples, and I've also written many UPDATE statements that are expected to update exactly one row, but I find an ASSERT would be adequate for that. I'm *not* doing FOR-loops with UPDATE of single rows. I typically have functions which have an input variable, which maps to a primary key in a table, and the UPDATE is made on that single row. This is a simplificaiton, but the main point is that the typical use case is *not* FOR-loops. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian br...@momjian.us wrote: SINGLETON UPDATE ...? Does it come with built-in spell check? :-) It's a bit long to write. I like STRICT, that maps good to what we already have with SELECT ... INTO STRICT. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Tue, Sep 2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian br...@momjian.us wrote: SINGLETON UPDATE ...? Does it come with built-in spell check? :-) It's a bit long to write. I like STRICT, that maps good to what we already have with SELECT ... INTO STRICT. Spell checker coming in plpgsql3! ;-) Anyway, as you have seen, the problem is not creating plpgsql2 --- you could do that yourself on your own and distribute it. What you want is for the community to develop/maintain it, and as you have also seen, everyone uses plpgsql slightly differently, so if you want the community to maintain it, you are only going to get some of the things you want. Of course, the idea of upgrading plpgsql is long overdue, so it is very good we are all talking about it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
