Re: [HACKERS] TABLESAMPLE patch
On 10/04/15 21:26, Peter Eisentraut wrote: On 4/9/15 8:58 PM, Petr Jelinek wrote: Well, you can have two approaches to this, either allow some specific set of keywords that can be used to specify limit, or you let sampling methods interpret parameters, I believe the latter is more flexible. There is nothing stopping somebody writing sampling method which takes limit as number of rows, or anything else. Also for example for BERNOULLI to work correctly you'd need to convert the number of rows to fraction of table anyway (and that's exactly what the one database which has this feature does internally) and then it's no different than passing (SELECT 100/reltuples*number_of_rows FROM tablename) as a parameter. What is your intended use case for this feature? I know that give me 100 random rows from this table quickly is a common use case, but that's kind of cumbersome if you need to apply formulas like that. I'm not sure what the use of a percentage is. Presumably, the main use of this features is on large tables. But then you might not even know how large it really is, and even saying 0.1% might be more than you wanted to handle. My main intended use-case is analytics on very big tables. The percentages of population vs confidence levels are pretty well mapped there and you can get quite big speedups if you are fine with getting results with slightly smaller confidence (ie you care about ballpark figures). But this was not really my point, the BERNOULLI just does not work well with row-limit by definition, it applies probability on each individual row and while you can get probability from percentage very easily (just divide by 100), to get it for specific target number of rows you have to know total number of source rows and that's not something we can do very accurately so then you won't get 500 rows but approximately 500 rows. In any case for give me 500 somewhat random rows from table quickly you want probably SYSTEM sampling anyway as it will be orders of magnitude faster on big tables and yes even 0.1% might be more than you wanted in that case. I am not against having row limit input for methods which can work with it like SYSTEM but then that's easily doable by adding separate sampling method which accepts rows (even if sampling algorithm itself is same). In current approach all you'd have to do is write different init function for the sampling method and register it under new name (yes it won't be named SYSTEM but for example SYSTEM_ROWLIMIT then). -- Petr Jelinek 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] TABLESAMPLE patch
On 04/10/15 21:57, Petr Jelinek wrote: On 10/04/15 21:26, Peter Eisentraut wrote: But this was not really my point, the BERNOULLI just does not work well with row-limit by definition, it applies probability on each individual row and while you can get probability from percentage very easily (just divide by 100), to get it for specific target number of rows you have to know total number of source rows and that's not something we can do very accurately so then you won't get 500 rows but approximately 500 rows. It's actually even trickier. Even if you happen to know the exact number of rows in the table, you can't just convert that into a percentage like that and use it for BERNOULLI sampling. It may give you different number of result rows, because each row is sampled independently. That is why we have Vitter's algorithm for reservoir sampling, which works very differently from BERNOULLI. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, 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] TABLESAMPLE patch
On 10/04/15 22:16, Tomas Vondra wrote: On 04/10/15 21:57, Petr Jelinek wrote: On 10/04/15 21:26, Peter Eisentraut wrote: But this was not really my point, the BERNOULLI just does not work well with row-limit by definition, it applies probability on each individual row and while you can get probability from percentage very easily (just divide by 100), to get it for specific target number of rows you have to know total number of source rows and that's not something we can do very accurately so then you won't get 500 rows but approximately 500 rows. It's actually even trickier. Even if you happen to know the exact number of rows in the table, you can't just convert that into a percentage like that and use it for BERNOULLI sampling. It may give you different number of result rows, because each row is sampled independently. That is why we have Vitter's algorithm for reservoir sampling, which works very differently from BERNOULLI. Hmm this actually gives me idea - perhaps we could expose Vitter's reservoir sampling as another sampling method for people who want the give me 500 rows from table fast then? We already have it implemented it's just matter of adding the glue. -- Petr Jelinek 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] raw output from copy
Hi
I wrote a prototype of this patch, and it works well
postgres=# set client_encoding to 'latin2';
SET
Time: 1.488 ms
postgres=# \copy (select xmlelement(name xx, d) from d) to ~/d.xml (format
'raw')
COPY 1
Time: 1.108 ms
postgres=# copy (select xmlelement(name xx, d) from d) to stdout (format
'raw') ;
?xml version=1.0 encoding=LATIN2?xxpříliš žluťoučký kůň/xxTime:
1.000 ms
Regards
Pavel
2015-04-09 20:48 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com:
Hi
This thread was finished without real work. I have a real use case -
export XML doc in non utf8 encoding.
http://www.postgresql.org/message-id/16174.1319228...@sss.pgh.pa.us
I propose to implement new format option RAW like Tom proposed.
It requires only one row, one column result - and result is just raw
binary data without size.
Objections? Ideas?
Regards
Pavel
commit 60c6701fe5a91c41e9ed0db99676c8b1a27e85e3
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Fri Apr 10 23:22:39 2015 +0200
initial
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 92ff632..5701f8b 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -113,6 +113,7 @@ typedef struct CopyStateData
char *filename; /* filename, or NULL for STDIN/STDOUT */
bool is_program; /* is 'filename' a program to popen? */
bool binary; /* binary format? */
+ bool raw; /* raw format - data only */
bool oids; /* include OIDs? */
bool freeze; /* freeze rows on loading? */
bool csv_mode; /* Comma Separated Value format? */
@@ -348,6 +349,13 @@ SendCopyBegin(CopyState cstate)
int16 format = (cstate-binary ? 1 : 0);
int i;
+ if (cstate-binary)
+ format = 1;
+ else if (cstate-raw)
+ format = 2;
+ else
+ format = 0;
+
pq_beginmessage(buf, 'H');
pq_sendbyte(buf, format); /* overall format */
pq_sendint(buf, natts, 2);
@@ -359,7 +367,7 @@ SendCopyBegin(CopyState cstate)
else if (PG_PROTOCOL_MAJOR(FrontendProtocol) = 2)
{
/* old way */
- if (cstate-binary)
+ if (cstate-binary || cstate-raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(COPY BINARY is not supported to stdout or from stdin)));
@@ -371,7 +379,7 @@ SendCopyBegin(CopyState cstate)
else
{
/* very old way */
- if (cstate-binary)
+ if (cstate-binary || cstate-raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(COPY BINARY is not supported to stdout or from stdin)));
@@ -485,7 +493,7 @@ CopySendEndOfRow(CopyState cstate)
switch (cstate-copy_dest)
{
case COPY_FILE:
- if (!cstate-binary)
+ if (!(cstate-binary || cstate-raw))
{
/* Default line termination depends on platform */
#ifndef WIN32
@@ -543,7 +551,7 @@ CopySendEndOfRow(CopyState cstate)
break;
case COPY_NEW_FE:
/* The FE/BE protocol uses \n as newline for all platforms */
- if (!cstate-binary)
+ if (!(cstate-binary || cstate-raw))
CopySendChar(cstate, '\n');
/* Dump the accumulated row as one CopyData message */
@@ -1005,6 +1013,8 @@ ProcessCopyOptions(CopyState cstate,
cstate-csv_mode = true;
else if (strcmp(fmt, binary) == 0)
cstate-binary = true;
+ else if (strcmp(fmt, raw) == 0)
+cstate-raw = true;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1808,6 +1818,10 @@ CopyTo(CopyState cstate)
num_phys_attrs = tupDesc-natts;
cstate-null_print_client = cstate-null_print; /* default */
+ /* don't allow more columns for raw format */
+ if (tupDesc-natts 1)
+ elog(ERROR, too much columns for RAW output);
+
/* We use fe_msgbuf as a per-row buffer regardless of copy_dest */
cstate-fe_msgbuf = makeStringInfo();
@@ -1819,7 +1833,7 @@ CopyTo(CopyState cstate)
Oid out_func_oid;
bool isvarlena;
- if (cstate-binary)
+ if ((cstate-binary || cstate-raw))
getTypeBinaryOutputInfo(attr[attnum - 1]-atttypid,
out_func_oid,
isvarlena);
@@ -1858,7 +1872,7 @@ CopyTo(CopyState cstate)
tmp = 0;
CopySendInt32(cstate, tmp);
}
- else
+ else if (!cstate-raw)
{
/*
* For non-binary copy, we need to convert null_print to file
@@ -1970,7 +1984,7 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
CopySendInt32(cstate, tupleOid);
}
}
- else
+ else if (!cstate-raw)
{
/* Text format has no per-tuple header, but send OID if wanted */
/* Assume digits don't need any quoting or encoding conversion */
@@ -1998,14 +2012,16 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
if (isnull)
{
- if (!cstate-binary)
+ if (cstate-raw)
+elog(ERROR, cannot to push NULL in raw output);
+ else if (!cstate-binary)
CopySendString(cstate, cstate-null_print_client);
else
CopySendInt32(cstate, -1);
}
else
{
- if (!cstate-binary)
+ if (!(cstate-binary || cstate-raw))
{
string = OutputFunctionCall(out_functions[attnum - 1],
value);
diff
Re: [HACKERS] Doubt about AccessExclusiveLock in ALTER TABLE .. SET ( .. );
On Tue, Apr 7, 2015 at 10:57 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
On Tue, Apr 7, 2015 at 10:15 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Fabrízio de Royes Mello wrote:
On Mon, Apr 6, 2015 at 12:53 AM, Alvaro Herrera
alvhe...@2ndquadrant.com
wrote:
Fabrízio de Royes Mello wrote:
Ok guys. The attached patch refactor the reloptions adding a new
field
lockmode in relopt_gen struct and a new method to determine
the
required lock level from an option list.
We need determine the appropriate lock level for each reloption:
I don't think AccessShareLock is appropriate for any option
change. You
should be using a lock level that's self-conflicting, as a minimum
requirement, to avoid two processes changing the value concurrently.
What locklevel do you suggest? Maybe ShareLock?
ShareUpdateExclusive probably. ShareLock doesn't conflict with itself.
Ok.
(I would probably go as far as ensuring that the lock level
specified in
the table DoLockModesConflict() with itself in an Assert somewhere.)
If I understood this is to check if the locklevel of the reloption
list
don't conflict one each other, is it?
I mean
Assert(DoLockModesConflict(relopt_gen-locklevel,
relopt_gen-locklevel));
Understood... IMHO the better place to perform this assert is in
initialize_reloptions.
Thoughts?
The attached patch:
- introduce new field lockmode to relopt_gen struct
- initialize lockmode with ShareUpdateExclusiveLock to autovac settings
- add assert to ensuring that the lock level specified don't conflict with
itself
- add function GetRelOptionsLockLevel to determine the required lock mode
from an option list
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 8176b6a..f273944 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -57,7 +57,8 @@ static relopt_bool boolRelOpts[] =
{
autovacuum_enabled,
Enables autovacuum in this relation,
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
true
},
@@ -65,7 +66,8 @@ static relopt_bool boolRelOpts[] =
{
user_catalog_table,
Declare a table as an additional catalog table, e.g. for the purpose of logical replication,
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ AccessExclusiveLock
},
false
},
@@ -73,7 +75,8 @@ static relopt_bool boolRelOpts[] =
{
fastupdate,
Enables \fast update\ feature for this GIN index,
- RELOPT_KIND_GIN
+ RELOPT_KIND_GIN,
+ AccessExclusiveLock
},
true
},
@@ -81,7 +84,8 @@ static relopt_bool boolRelOpts[] =
{
security_barrier,
View acts as a row security barrier,
- RELOPT_KIND_VIEW
+ RELOPT_KIND_VIEW,
+ AccessExclusiveLock
},
false
},
@@ -95,7 +99,8 @@ static relopt_int intRelOpts[] =
{
fillfactor,
Packs table pages only to this percentage,
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ AccessExclusiveLock
},
HEAP_DEFAULT_FILLFACTOR, HEAP_MIN_FILLFACTOR, 100
},
@@ -103,7 +108,8 @@ static relopt_int intRelOpts[] =
{
fillfactor,
Packs btree index pages only to this percentage,
- RELOPT_KIND_BTREE
+ RELOPT_KIND_BTREE,
+ AccessExclusiveLock
},
BTREE_DEFAULT_FILLFACTOR, BTREE_MIN_FILLFACTOR, 100
},
@@ -111,7 +117,8 @@ static relopt_int intRelOpts[] =
{
fillfactor,
Packs hash index pages only to this percentage,
- RELOPT_KIND_HASH
+ RELOPT_KIND_HASH,
+ AccessExclusiveLock
},
HASH_DEFAULT_FILLFACTOR, HASH_MIN_FILLFACTOR, 100
},
@@ -119,7 +126,8 @@ static relopt_int intRelOpts[] =
{
fillfactor,
Packs gist index pages only to this percentage,
- RELOPT_KIND_GIST
+ RELOPT_KIND_GIST,
+ AccessExclusiveLock
},
GIST_DEFAULT_FILLFACTOR, GIST_MIN_FILLFACTOR, 100
},
@@ -127,7 +135,8 @@ static relopt_int intRelOpts[] =
{
fillfactor,
Packs spgist index pages only to this percentage,
- RELOPT_KIND_SPGIST
+ RELOPT_KIND_SPGIST,
+ AccessExclusiveLock
},
SPGIST_DEFAULT_FILLFACTOR, SPGIST_MIN_FILLFACTOR, 100
},
@@ -135,7 +144,8 @@ static relopt_int intRelOpts[] =
{
autovacuum_vacuum_threshold,
Minimum number of tuple updates or deletes prior to vacuum,
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0, INT_MAX
},
@@ -143,7 +153,8 @@ static relopt_int intRelOpts[] =
{
autovacuum_analyze_threshold,
Minimum number of tuple inserts, updates or deletes prior to analyze,
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+
Re: [HACKERS] PATCH:do not set Win32 server-side socket buffer size on windows 2012
At 2015-04-10 20:00:35, Michael Paquier michael.paqu...@gmail.com wrote: Interesting. I think that for the time being you should add it to the next commit fest to target an integration in 9.6 as these days we are busy wrapping up the last commit fest of 9.5: https://commitfest.postgresql.org/5/ I had add it to the commit fest. https://commitfest.postgresql.org/5/212/ Best Regards, Chen Huajun
