Re: [HACKERS] proposal: session server side variables

[Pavel Stehule]

2016-12-26 8:30 GMT+01:00 Fabien COELHO :

>
> Hello Jim,
>
> If you want to ignore performance, there are things you can do with
>> non-transactional variables that are simply not possible with tables. But
>> even ignoring that, the performance cost of temp tables is massive compared
>> to variables.
>>
>
> Ok, then read "variables are like tables" instead of "variables are
> tables". Note that I'm definitely interested in performance in the end, but
> I'm first interested in discussing features.
>
> Currently I'm unconvinced by a proposal of variables that are in the
> relation namespace (pg_class), are subject to GRANT/REVOKE, are managed
> with CREATE/DROP *but* are somehow partly non transactional, and only alive
> through sessions, and are only accessed through functions...
>
> My current thinking is that the proposed design is either too heavy
> (pg_class, permissions) or too light (then why not possible persistence
> and/or transactions?): I would be more at ease with very light-weight
> typed? session variables stored in the server process "as is", without any
> attempt at pg_class & permissions, or with more full featured variables,
> but not something half-baked which seems designed for a particular use case
> that I do not have.
>
> Not only is the access far more complex, but bloating is a major problem
>> (both in the table itself as well as in the catalog). That's part of the
>> driver for all the discussion about things like permanent temp tables
>> (which still leaves a bloat and performance problem in the table itself).
>>
>
> If a variable as currently discussed is in pg_class and subject to
> permissions, then probably it will cost on the catalog side anyway, and at
> least their existent would be transactional even if their value is not.
>

the access right check has **constant** small cost (check in object cache).
The proposed variables has not any negative effect on catalogue bloating
because a metadata are persistent.

It is reason why I use statement "CREATE VARIABLE", not "DECLARE VARIABLE"

Regards

Pavel






>
> --
> Fabien.
>

Re: [HACKERS] proposal: session server side variables

[Fabien COELHO]

Hello Jim,

If you want to ignore performance, there are things you can do with 
non-transactional variables that are simply not possible with tables. But 
even ignoring that, the performance cost of temp tables is massive compared 
to variables.


Ok, then read "variables are like tables" instead of "variables are 
tables". Note that I'm definitely interested in performance in the end, 
but I'm first interested in discussing features.


Currently I'm unconvinced by a proposal of variables that are in the 
relation namespace (pg_class), are subject to GRANT/REVOKE, are managed 
with CREATE/DROP *but* are somehow partly non transactional, and only 
alive through sessions, and are only accessed through functions...


My current thinking is that the proposed design is either too heavy 
(pg_class, permissions) or too light (then why not possible persistence 
and/or transactions?): I would be more at ease with very light-weight 
typed? session variables stored in the server process "as is", without any 
attempt at pg_class & permissions, or with more full featured variables, 
but not something half-baked which seems designed for a particular use 
case that I do not have.


Not only is the access far more complex, but bloating is a major problem 
(both in the table itself as well as in the catalog). That's part of the 
driver for all the discussion about things like permanent temp tables 
(which still leaves a bloat and performance problem in the table 
itself).


If a variable as currently discussed is in pg_class and subject to 
permissions, then probably it will cost on the catalog side anyway, and at 
least their existent would be transactional even if their value is not.


--
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] Incautious handling of overlength identifiers

[Michael Paquier]

On Sat, Dec 24, 2016 at 7:44 AM, Joe Conway  wrote:
> On 12/23/2016 12:44 PM, Tom Lane wrote:
>> I wrote:
>>> So what to do?  We could run around and fix these individual cases
>>> and call it good, but if we do, I will bet a very fine dinner that
>>> more such errors will sneak in before long.  Seems like we need a
>>> coding convention that discourages just randomly treating a C string
>>> as a valid value of type NAME.  Not sure how to get there though.
>>
>> An alternative worth considering, especially for the back branches,
>> is simply to remove the Assert in hashname().  That would give us
>> the behavior that non-developers see anyway, which is that these
>> functions always fail to match overlength names, whether or not
>> the names would have matched after truncation.  Trying to apply
>> truncation more consistently could be left as an improvement
>> project for later.
>
> That sounds reasonable to me.

+1 for just removing the assertion on back-branches. On HEAD, it seems
right to me to keep the assertion. However it is not possible to just
switch those routines from text to name as a table could be defined
with its schema name. So at minimum this would require adjusting
textToQualifiedNameList() & similar routines in charge of putting in
shape the name lists. Another idea would be to have as a data type an
idea of "qualified name", where the schema and the table names are
truncated automatically at 63 characters, and have those catalog use
it. This way the parsing and truncation logic are directly part of the
input and output functions, and we could assume that the internal
representation is a list of names.
-- 
Michael


-- 
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: Write Amplification Reduction Method (WARM)

[Jaime Casanova]

On 2 December 2016 at 07:36, Pavan Deolasee  wrote:
>
> I've updated the patches after fixing the issue. Multiple rounds of
> regression passes for me without any issue. Please let me know if it works
> for you.
>

Hi Pavan,

Today i was playing with your patch and running some tests and found
some problems i wanted to report before i forget them ;)

* You need to add a prototype in src/backend/utils/adt/pgstatfuncs.c:
extern Datum pg_stat_get_tuples_warm_updated(PG_FUNCTION_ARGS);

* The isolation test for partial_index fails (attached the regression.diffs)

* running a home-made test i have at hand i got this assertion:
"""
TRAP: FailedAssertion("!(buf_state & (1U << 24))", File: "bufmgr.c", Line: 837)
LOG:  server process (PID 18986) was terminated by signal 6: Aborted
"""
To reproduce:
1) run prepare_test.sql
2) then run the following pgbench command (sql scripts attached):
pgbench -c 24 -j 24 -T 600 -n -f inserts.sql@15 -f updates_1.sql@20 -f
updates_2.sql@20 -f deletes.sql@45 db_test


* sometimes when i have made the server crash the attempt to recovery
fails with this assertion:
"""
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/157F970
TRAP: FailedAssertion("!(!warm_update)", File: "heapam.c", Line: 8924)
LOG:  startup process (PID 14031) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
"""
still cannot reproduce this one consistently but happens often enough

will continue playing with it...

-- 
Jaime Casanova  www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


regression.diffs
Description: Binary data


deletes.sql
Description: application/sql


inserts.sql
Description: application/sql


prepare_test.sql
Description: application/sql


updates_1.sql
Description: application/sql


updates_2.sql
Description: application/sql

-- 
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] pg_stat_activity.waiting_start

[Joel Jacobson]

On Sun, Dec 25, 2016 at 8:01 PM, Andres Freund  wrote:
> On December 25, 2016 1:21:43 AM GMT+01:00, Joel Jacobson  
> wrote:
>
>>Is it really a typical real-life scenario that processes can be
>>waiting extremely often for extremely short periods of time,
>>where the timing overhead would be significant?
>
> Yes. Consider WAL insertion, procarray or other similar contended locks.

Ah, I see, then I understand it has to be blazingly fast.

Maybe a good tradeoff then would be to let "wait_start" represent the
very first time the txn started waiting?
That way gettimeofday() would only be called once per txn, and the
value would be remembered, but not exposed when the txn is not
waiting.
If the txn is waiting/not waiting multiple times during it's
life-time, the same "wait_start" value would be exposed when it's
waiting, and NULL when it's not. Sounds good?

As long as the documentation is clear on "wait_start" meaning when the
first wait started in the txn, I think that's useful enough to improve
the situation, as one could then ask a query like "select all
processes that have possibly been waiting for at least 5 seconds",
which you cannot do today.

The best you can do today is ask a query like "select all processes
that are waiting and have been running for at least 5 seconds", but
during those 5 seconds they have been running, they might only have
been waiting for the very last few milliseconds, which might not be a
problem at all. If instead knowing they were waiting 5 seconds ago,
and are still waiting, but might have had periods in between where
they were not waiting, I would say that is close enough to what I as a
user want to know, and can use that information for automatic
decision-making on e.g. if I want to terminate other blocking
processes.


-- 
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] Cluster wide option to control symbol case folding

[Craig Ringer]

On 25 Dec. 2016 14:49, "Tom Lane"  wrote:



No.  This has been looked into repeatedly in the past, and we simply
don't want to deal with it.  Quite aside from the impact on the server
(which would be extensive), it would break every nontrivial application,
and force them all to try to deal with each possible folding behavior.
The more behaviors there are, the worse that gets.


Good point. That's been a source of pain for standard_conforming_strings
and bytea_output. Many apps are also - sadly - hopelessly broken with
regards to text encoding. (Including, unfortunately, PostgreSQL it's self,
but more subtly than the obvious brain-dead behaviour of many apps).

Tom makes a good point. Apps that need to care are better off consistently
double quoting.

Frankly if we were going to add any option at all I'd want one to force all
unquoted identifiers to ERROR so apps could be easily validated not to
depend on case folding behaviour at all. But even that has issues and is
probably better done in tooling and static analysis or via a plugin.

Re: [HACKERS] comments tablecmds.c

[Erik Rijkers]

On 2016-12-25 13:38, Erik Rijkers wrote:

'the the' -> 'the'

and

'ie' -> 'i.e.'

Although (concening the latter change) the present counts are 'ie'
428, and 'i.e.' 428.
so it might be debatable (but let's not)



Sorry; I meant:  'ie' 428, and 'i.e.' 305.




--
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] pg_stat_activity.waiting_start

[Andres Freund]

On December 25, 2016 1:21:43 AM GMT+01:00, Joel Jacobson  
wrote:

>Is it really a typical real-life scenario that processes can be
>waiting extremely often for extremely short periods of time,
>where the timing overhead would be significant?

Yes. Consider WAL insertion, procarray or other similar contended locks.

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] comments tablecmds.c

[Erik Rijkers]

'the the' -> 'the'

and

'ie' -> 'i.e.'

Although (concening the latter change) the present counts are 'ie' 428, 
and 'i.e.' 428.

so it might be debatable (but let's not)

thanks,

Erik Rijkers

--- src/backend/commands/tablecmds.c.orig	2016-12-25 13:29:28.715237491 +0100
+++ src/backend/commands/tablecmds.c	2016-12-25 13:31:03.24936 +0100
@@ -13244,7 +13244,7 @@
 			skip_validate = true;
 
 		/*
-		 * We choose to err on the safer side, ie, give up on skipping the the
+		 * We choose to err on the safer side, i.e., give up on skipping the
 		 * validation scan, if the partition key column doesn't have the NOT
 		 * NULL constraint and the table is to become a list partition that
 		 * does not accept nulls.  In this case, the partition predicate

-- 
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] Cluster wide option to control symbol case folding

[Lewis, Ian (Microstar Laboratories)]

Tom Lane [mailto:t...@sss.pgh.pa.us] wrote:
>  Quite aside from the impact on the server (which would be extensive),
it would break every nontrivial application, and force them all to try
to deal with each possible folding behavior.
I have read through the various threads related to this issue that you
supplied. And, it looks quite clear that a change is unlikely. Maybe it
is even a bad idea, though personally I think it could prove worth the
pain that you obviously anticipate.

However, before fully dropping this issue, I do have one comment I would
like to make (just for the record) on your statement that a change in
the current behavior would break most non-trivial applications.

I assume you are talking about general purpose tools that attempt to
interact with any database in any configuration. Obviously, a purpose
built tool, such as our own internal database applications, would be
designed only for the behavior of the databases it is intended to work
against.

I have, over the past few months, tried quite a large number (10, maybe
a few more than that) of general purpose tools against PostgreSQL
looking for replacements for some of the tools we use against our old
database server. And, almost none of them work well if I quote
identifiers on the server. Almost all work perfectly well if I accept
that all my symbols will be converted to lower case and do not quote the
identifiers. Most fail - often not even in corner cases - when the
catalog (properly) returns a mixed case symbol like WeeklySales. Very
few of the tools I have tried seem to know to quote such a symbol to
preserve the case when generating a query to send back to the server. 

Most of the tools I have tested work through ODBC, though a few connect
directly to PostgreSQL. Both types of tools have exhibited similar
issues. Maybe the ODBC connection is relevant, since it appears that
Microsoft SQL Server does what our current server does and preserves
case, at least in some modes. A good fraction of the tools I have tried,
push their compatibility with SQL Server.

So, the current behavior already breaks many tools unless one accepts
that all symbols on the server are lower case. At root, based on reading
the threads you provided, this probably indicates defects in the tools,
rather than a problem with PostgreSQL. My reading of the standard text
quoted in various places is that any mixed case identifier returned from
the catalog has to be quoted to match in a query (whether you fold to
lower or upper case). 

But, I can easily imagine a good number of people deciding they want
mixed case on the server, and so quoting their identifiers. And, then
deciding PostgreSQL is defective, rather than deciding their favorite
administration or query tool is defective. Almost all of the tools I
tried worked fine when I had all lower case symbols on the server. Based
on observing the generated SQL, most of the tools that failed for me
when I had mixed case symbols on the server would work against a case
preserving mode in PostgreSQL. The tools generally pass through the
catalog reported symbols without manipulation. 

I fully understand your concern to keep the number of modal behaviors to
a minimum. And, the upper case folding looks to have a lot of side
effects, and so is a whole lot harder to implement than I expected.
Preserving case, which is what we actually want, by contrast still looks
pretty easy, though it is clearly not standard compliant. 

I do not have any more to add on the subject. I just wanted to make this
note that a good fraction of third party tools already fail unless one
never quotes identifiers on PostgreSQL. 

Thank you for taking the time to reply to my original inquiry. 

Ian Lewis
www.mstarlabs.com




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers