Re: [HACKERS] COMPRESS VALUES feature request

On Tue, May 9, 2017 at 8:18 PM, Erez Segal wrote: > In the IRC channel - johto suggested an implementation: > > if you want to get really fancy you could have two columns where > only one of set; one would be the value (if reasonably unique) and the > other the id (if not)

[HACKERS] COMPRESS VALUES feature request

Hi, Following a discussion in the IRC channel, I'd like to suggest a feature request to the ToDo WIKI page . The use case is having a column with strings/floats, where some values are very common and can be compressed, while many other values are unique,

Re: [HACKERS] Passing values to a dynamic background worker

On Tue, Apr 18, 2017 at 12:34 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 4/17/17 16:19, Keith Fiske wrote: > > I've reached a roadblock in that bgw_main_arg can only accept a single > > argument that must be passed by value for a dynamic bgw. I already > > worked around

Re: [HACKERS] Passing values to a dynamic background worker

On 4/17/17 16:19, Keith Fiske wrote: > I've reached a roadblock in that bgw_main_arg can only accept a single > argument that must be passed by value for a dynamic bgw. I already > worked around this for passing the database name to the my existing use > of a bgw with doing partition maintenance

Re: [HACKERS] Passing values to a dynamic background worker

On Tue, Apr 18, 2017 at 5:40 AM, Amit Langote wrote: > On 2017/04/18 18:12, Kyotaro HORIGUCHI wrote: > > At Mon, 17 Apr 2017 16:19:13 -0400, Keith Fiske wrote: > >> So after reading a recent thread on the steep learning curve for PG > >> internals [1], I figured

Re: [HACKERS] Passing values to a dynamic background worker

On 2017/04/18 18:12, Kyotaro HORIGUCHI wrote: > At Mon, 17 Apr 2017 16:19:13 -0400, Keith Fiske wrote: >> So after reading a recent thread on the steep learning curve for PG >> internals [1], I figured I'd share where I've gotten stuck with this in a >> new thread vs hijacking that one. >> >> One

Re: [HACKERS] Passing values to a dynamic background worker

Hello, At Mon, 17 Apr 2017 16:19:13 -0400, Keith Fiske wrote in > So after reading a recent thread on the steep learning curve for PG > internals [1], I figured I'd share where I've gotten stuck with this in

[HACKERS] Passing values to a dynamic background worker

So after reading a recent thread on the steep learning curve for PG internals [1], I figured I'd share where I've gotten stuck with this in a new thread vs hijacking that one. One of the goals I had with pg_partman was to see if I could get the partitioning python scripts redone as C functions

Re: [HACKERS] Insert values() per-statement overhead

On 2016-01-15 13:17:12 +0300, Vladimir Sitnikov wrote: > There is a finding that insert(x) values(y);insert(x) values(z); is > 2-4 times slower than insert(..) values(y),(z); > see [1], [2]. If you indeed just mean statements like above, without begin/commit, a large portion of the overhead will

[HACKERS] Insert values() per-statement overhead

Hi, There is a finding that insert(x) values(y);insert(x) values(z); is 2-4 times slower than insert(..) values(y),(z); see [1], [2]. In other words, there is a significant per-statement overhead even though server-prepared statements are properly used. The issue is reproducible in 9.5rc1. Is

Re: [HACKERS] Insert values() per-statement overhead

>I guess you mean there's a transaction surrounding it? Sure there is a transaction. I measure the latency from the first Bind message to the ReadyForQuery response. The database is at localhost. The flow is as follows (I've use 4 queries in batch for brevity, however the test above is executed

Re: [HACKERS] uninitialized values in revised prepared xact code

On 10/11/2014 10:20 PM, Bruce Momjian wrote: Uh, was this fixed. I see a cleanup commit for this C file, but this report is from June: commit 07a4a93a0e35a778c77ffbbbc18de29e859e18f0 Author: Heikki Linnakangas heikki.linnakan...@iki.fi Date: Fri May 16 09:47:50 2014

Re: [HACKERS] uninitialized values in revised prepared xact code

Uh, was this fixed. I see a cleanup commit for this C file, but this report is from June: commit 07a4a93a0e35a778c77ffbbbc18de29e859e18f0 Author: Heikki Linnakangas heikki.linnakan...@iki.fi Date: Fri May 16 09:47:50 2014 +0300 Initialize tsId and

[HACKERS] uninitialized values in revised prepared xact code

Hi, I've just rerun valgrind for the first time in a while and saw the following splat. My guess is it exists since bb38fb0d43c, but that's blindly guessing: ==2049== Use of uninitialised value of size 8 ==2049==at 0x4FE66D: EndPrepare (twophase.c:1063) ==2049==by 0x4F231B:

[HACKERS] Simplified VALUES parameters

Hi, I'm the maintainer and a primary author of a postgresql client library for Haskell, called postgresql-simple, and I recently investigated improving support for VALUES expressions in this library. As a result, I'd like to suggest two changes to postgresql: 1. Allow type specifications

Re: [HACKERS] Simplified VALUES parameters

On 02/26/2014 10:47 AM, Leon Smith wrote: Hi, I'm the maintainer and a primary author of a postgresql client library for Haskell, called postgresql-simple, and I recently investigated improving support for VALUES expressions in this library. As a result, I'd like to suggest two changes to

Re: [HACKERS] Simplified VALUES parameters

On Wed, Feb 26, 2014 at 1:54 PM, Josh Berkus j...@agliodbs.com wrote: And thank you for writing that driver! You are welcome! I have no opinion about your request for VALUES() stuff, though. It looks fairly complex as far as grammar and libpq is concerned. Actually, my suggestions

Re: [HACKERS] Simplified VALUES parameters

Leon Smith wrote Hi, I'm the maintainer and a primary author of a postgresql client library for Haskell, called postgresql-simple, and I recently investigated improving support for VALUES expressions in this library. As a result, I'd like to suggest two changes to postgresql: 1.

[HACKERS] Decimal values in

Dear Dev-List, inside execProcnode.c's ExecProcNode method we want to extract the value of a tuple for a specific attribute. This works great for integers and strings, but we are not able to figure out how to do this for floating point numbers. Below is some example code snippet to show our

Re: [HACKERS] Decimal values in

Masterprojekt Naumann1 escribió: Dear Dev-List, inside execProcnode.c's ExecProcNode method we want to extract the value of a tuple for a specific attribute. This works great for integers and strings, but we are not able to figure out how to do this for floating point numbers. Below is some

Re: [HACKERS] Decimal values in

Alvaro Herrera alvhe...@2ndquadrant.com writes: Masterprojekt Naumann1 escribió: inside execProcnode.c's ExecProcNode method we want to extract the value of a tuple for a specific attribute. This works great for integers and strings, but we are not able to figure out how to do this for

[HACKERS] Unitialized Values in record_image_cmp

Hi, Valgrind tells me: ==442828== Conditional jump or move depends on uninitialised value(s) ==442828==at 0x80084F: record_image_cmp (rowtypes.c:1521) ==442828==by 0x801522: btrecordimagecmp (rowtypes.c:1839) ==442828==by 0x8C6604: comparison_shim (sortsupport.c:53) ==442828==by

Re: [HACKERS] Unitialized Values in record_image_cmp

On Thu, Oct 17, 2013 at 8:01 AM, Andres Freund and...@2ndquadrant.com wrote: Hi, Valgrind tells me: ==442828== Conditional jump or move depends on uninitialised value(s) ==442828==at 0x80084F: record_image_cmp (rowtypes.c:1521) ==442828==by 0x801522: btrecordimagecmp

Re: [HACKERS] Unitialized Values in record_image_cmp

On 2013-10-17 08:16:58 -0500, Merlin Moncure wrote: On Thu, Oct 17, 2013 at 8:01 AM, Andres Freund and...@2ndquadrant.com wrote: Hi, Valgrind tells me: ==442828== Conditional jump or move depends on uninitialised value(s) ==442828==at 0x80084F: record_image_cmp (rowtypes.c:1521)

Re: [HACKERS] return values of backend sub-main functions

On ons, 2012-01-18 at 21:21 +0200, Peter Eisentraut wrote: On lör, 2012-01-07 at 16:41 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I suggest that we change PostgresMain(), PostmasterMain(), BackendRun(), WalSenderMain(), and WalSndLoop() to return void as well. I

Re: [HACKERS] return values of backend sub-main functions

On Tue, Jun 19, 2012 at 7:31 AM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2012-01-18 at 21:21 +0200, Peter Eisentraut wrote: On lör, 2012-01-07 at 16:41 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I suggest that we change PostgresMain(), PostmasterMain(),

Re: [HACKERS] return values of backend sub-main functions

On Tue, Jun 19, 2012 at 4:31 AM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2012-01-18 at 21:21 +0200, Peter Eisentraut wrote: On lör, 2012-01-07 at 16:41 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I suggest that we change PostgresMain(), PostmasterMain(),

[HACKERS] default values in inheritance hierarchies

Hi, I have two tables defined for example like this: create table a1 (id serial primary key, data text); create table a2 (id serial primary key, data2 integer) inherits (a1); The point is, that I want to have two tables with inheritance relation between them, but each with its own id

Re: [HACKERS] default values in inheritance hierarchies

Sorry, I have accidentaly sent incomplet mail. Here is the rest: The following sequence of commands will end up with error, because of duplicate ID in table a2: insert into a2(data2, data) values(456, 'jkl'); insert into a2(data2, data) values(789, 'mno'); Is there any way how to force

Re: [HACKERS] default values in inheritance hierarchies

=?ISO-8859-2?Q?Miroslav_=A9imul=E8=EDk?= simulcik.m...@gmail.com writes: Is there any way how to force UPDATE to use default value of ID column from table a2 when updating rows in this table, without using triggers? No. The update is expanded using the default expression applicable to the

Re: [HACKERS] return values of backend sub-main functions

On lör, 2012-01-07 at 16:41 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I suggest that we change PostgresMain(), PostmasterMain(), BackendRun(), WalSenderMain(), and WalSndLoop() to return void as well. I agree this code is not very consistent or useful, but one

[HACKERS] return values of backend sub-main functions

There is a bit of confusion around the return values and return protocols of the sub-main functions in the backend (PostgresMain etc.). Some functions are declared to return int but never return. It would be useful to make this consistent by either making them return void or making some use of

Re: [HACKERS] return values of backend sub-main functions

Peter Eisentraut pete...@gmx.net writes: I suggest that we change PostgresMain(), PostmasterMain(), BackendRun(), WalSenderMain(), and WalSndLoop() to return void as well. I agree this code is not very consistent or useful, but one question: what should the callers do if one of these functions

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

2010/10/3 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: 2010/10/2 Jeff Davis pg...@j-davis.com: On Fri, 2010-10-01 at 18:52 +0900, Hitoshi Harada wrote: While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT. From

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

Hitoshi Harada umi.tan...@gmail.com writes: DEFAULT is disallowed now in such VALUES list, but we can explain it is allowed in a simple VALUES of INSERT case. I don't think we really need to explain anything. This is per spec; if you trace the way that a DEFAULT expression can appear in

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

2010/10/4 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: DEFAULT is disallowed now in such VALUES list, but we can explain it is allowed in a simple VALUES of INSERT case. I don't think we really need to explain anything.  This is per spec; if you trace the way that

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

On Oct 3, 2010, at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hitoshi Harada umi.tan...@gmail.com writes: DEFAULT is disallowed now in such VALUES list, but we can explain it is allowed in a simple VALUES of INSERT case. I don't think we really need to explain anything. This is per spec;

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

2010/10/2 Jeff Davis pg...@j-davis.com: On Fri, 2010-10-01 at 18:52 +0900, Hitoshi Harada wrote: While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT. regression=# CREATE TABLE t1(x int); CREATE TABLE regression=# INSERT INTO t1 VALUES

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

Hitoshi Harada umi.tan...@gmail.com writes: 2010/10/2 Jeff Davis pg...@j-davis.com: On Fri, 2010-10-01 at 18:52 +0900, Hitoshi Harada wrote: While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT. From my reading the source around

[HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT. regression=# CREATE TABLE t1(x int); CREATE TABLE regression=# INSERT INTO t1 VALUES (1),(2),(3) LIMIT 1; INSERT 0 3 regression=# TABLE t1; x --- 1 2 3 (3 rows) regression=# TRUNCATE t1;

Re: [HACKERS] INSERT ... VALUES... with ORDER BY / LIMIT

On Fri, 2010-10-01 at 18:52 +0900, Hitoshi Harada wrote: While tackling the top-level CTEs patch, I found that INSERT ... VALUES isn't aware of ORDER BY / LIMIT. regression=# CREATE TABLE t1(x int); CREATE TABLE regression=# INSERT INTO t1 VALUES (1),(2),(3) LIMIT 1; INSERT 0 3 That looks

[HACKERS] WITH ... VALUES

Came across something interesting while looking at Marko Tiikkaja's cut-down WITH patch. I see that our grammar allows a WITH clause in front of VALUES, and analyze.c makes some effort to process it, but AFAICT there isn't any actual use case for this because you can't reference the WITH clause

Re: [HACKERS] WITH ... VALUES

On Fri, 2010-02-12 at 16:59 -0500, Tom Lane wrote: Came across something interesting while looking at Marko Tiikkaja's cut-down WITH patch. I see that our grammar allows a WITH clause in front of VALUES, and analyze.c makes some effort to process it, but AFAICT there isn't any actual use case

Re: [HACKERS] WITH ... VALUES

Jeff Davis pg...@j-davis.com writes: On Fri, 2010-02-12 at 16:59 -0500, Tom Lane wrote: AFAICT there isn't any actual use case for this because you can't reference the WITH clause from the body of VALUES: with tmp2 as (select a + 1 as b from tmp) values((select b from tmp2)); Ah,

[HACKERS] Writing values to relation using bytearray ...

Hi, I am trying to write values of different types to relation using following code. if(typbyval) { min_ba = (bytea *) palloc(len+1+VARHDRSZ); memcpy(VARDATA(min_ba), min_datum, len); SET_VARSIZE(min_ba, len+VARHDRSZ);

Re: [HACKERS] Writing values to relation using bytearray ...

On Fri, Mar 6, 2009 at 10:03 AM, Kedar Potdar kedar.pot...@gmail.com wrote: The aforementioned code works fine for types like int, data, text and I can read values from the relation correctly. The problem arises for type float8 which is not by value type and it has fixed length (8) where I

Re: [HACKERS] Writing values to relation using bytearray ...

Thanks Greg, for showing interest. The problem here is I need to store values of different types into bytearray column of relation. On Fri, Mar 6, 2009 at 4:33 PM, Greg Stark st...@enterprisedb.com wrote: On Fri, Mar 6, 2009 at 10:03 AM, Kedar Potdar kedar.pot...@gmail.com wrote: The

Re: [HACKERS] Writing values to relation using bytearray ...

On Fri, Mar 6, 2009 at 11:41 AM, Kedar Potdar kedar.pot...@gmail.com wrote: Thanks Greg, for showing interest. The problem here is I need to store values of different types into bytearray column of relation. Oh, hm. I think you need to look at typlen instead of typbyval. But you have an

Re: [HACKERS] Writing values to relation using bytearray ...

On Fri, Mar 6, 2009 at 12:01 PM, Greg Stark st...@enterprisedb.com wrote: But you have an additional problem for typbyval types: the pointer to Datum isn't necessarily pointing at the right bytes. I think you have to use the GET_[1248]_BYTES macros depending on typlen. There may be a helper

Re: [HACKERS] Writing values to relation using bytearray ...

Kedar Potdar kedar.pot...@gmail.com writes: The problem here is I need to store values of different types into bytearray column of relation. Perhaps you should study the ANALYZE code. AFAICS your requirements are not different from those of the pg_statistic data store. You should do things

Re: [HACKERS] Writing values to relation using bytearray ...

Thanks Tom for your interest. I could find a workaround for the issue wherein the value of type which is not stored by value and has fixed data length, is being stored in string format to the relation. While retrieving from relation, this value is converted by using coerce_to_specific_type() to

Re: [HACKERS] Writing values to relation using bytearray ...

Kedar Potdar kedar.pot...@gmail.com writes: I could find a workaround for the issue wherein the value of type which is not stored by value and has fixed data length, is being stored in string format to the relation. The answer is simple: don't do that. You do not need to, and should not,

[HACKERS] null values / partial indices

I noticed that the planner is unable to select an index scan when a partial index is available, the partial index is based on a NOT NULL condition. Example: start with no index: mydb=# EXPLAIN ANALYZE select id from str where url='foobar'; NOTICE: QUERY PLAN: Seq Scan on str (cost=0.00..88.91

Re: [HACKERS] null values / partial indices

mydb=# create index str_idx_url on str(url) where url is not null; CREATE mydb=# analyze str; ANALYZE mydb=# EXPLAIN ANALYZE select id from str where url='foobar'; NOTICE: QUERY PLAN: Seq Scan on str (cost=0.00..91.05 rows=3 width=4) (actual You can try an index like: create index

Re: [HACKERS] null values / partial indices

You can try an index like: create index str_idx_url on str(url) where url = ''; I think that should be identical. ('' is the smallest string, no ?) Thanks alot, it works now. But I still think the NOT NULL case would be useful. Best regards, Mario Weilguni

Re: [HACKERS] null values / partial indices

Mario Weilguni [EMAIL PROTECTED] writes: I noticed that the planner is unable to select an index scan when a partial index is available, the partial index is based on a NOT NULL condition. It wants you to do this: select id from str where url='foobar' and url is not null; I know and you know

Re: [HACKERS] Null values in indexes

Glad to hear GiST in 7.2 isn't broken :-) We miss the topic, what was the problem ? Do we need to fix GiST code for 7.3 ? proposal for null-safe GiST interface is available http://fts.postgresql.org/db/mw/msg.html?mid=1028327 and discussion http://fts.postgresql.org/db/mw/msg.html?mid=1025848

Re: [HACKERS] Null values in indexes

Oleg Bartunov [EMAIL PROTECTED] writes: Do we need to fix GiST code for 7.3 ? No, I think it's fine. I had forgotten that old discussion ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our

Re: [HACKERS] Null values in indexes

Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: How hard would it be to _not_ include nulls in indexes as they are not used anyway. Seems to me that would be a step backwards. It would cause multi-key indexes beeing unusable for partial key lookup. Imagine you have a key

Re: [HACKERS] Null values in indexes

Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: How hard would it be to _not_ include nulls in indexes as they are not used anyway. Seems to me that would be a step backwards. It would cause multi-key indexes beeing unusable for partial

Re: [HACKERS] Null values in indexes

-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 9:07 AM To: Jan Wieck Cc: Oleg Bartunov; Teodor Sigaev; [EMAIL PROTECTED] Subject: Re: [HACKERS] Null values in indexes Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: Hannu

Re: [HACKERS] Null values in indexes

To: Jan Wieck Cc: Oleg Bartunov; Teodor Sigaev; [EMAIL PROTECTED] Subject: Re: [HACKERS] Null values in indexes Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: How hard would it be to _not_ include nulls in indexes as they are not used

Re: [HACKERS] Null values in indexes

Urgh ... that means GiST indexing is actually broken, because GiST currently handles multicolumns but not nulls. Actually, it appears that 7.2 GiST does handle NULLs in columns after the first one, which I think is enough to avoid the problem Jan mentioned. The boolean column

[HACKERS] Null values in indexes

With 7.1.3, large indexes with null values allowed in one or more of the columns would cause crashes. (I have definitely seen this happen). Here is a project that mentions repairs: http://postgis.refractions.net/news/index.php?file=20020425.data Have repairs been effected in 7.2? Are they

Re: [HACKERS] Null values in indexes

Dann Corbit [EMAIL PROTECTED] writes: With 7.1.3, large indexes with null values allowed in one or more of the columns would cause crashes. (I have definitely seen this happen). Have repairs been effected in 7.2? Submit a test case and we'll tell you ... regards, tom

Re: [HACKERS] Null values in indexes

Hannu Krosing [EMAIL PROTECTED] writes: How hard would it be to _not_ include nulls in indexes as they are not used anyway. Seems to me that would be a step backwards. What should someday happen is to make IS NULL an indexable operator. The fact that we haven't got around to doing so is not