Re: [GENERAL] [HACKERS] SSL and Encryption

[Daniele Varrazzo]

On Fri, Nov 3, 2017 at 4:12 PM, Jeff Janes  wrote:
> On Thu, Nov 2, 2017 at 10:22 PM, John R Pierce  wrote:
>>
>> On 11/2/2017 10:12 PM, Jeff Janes wrote:
>>>
>>> https://wiki.postgresql.org/wiki/List_of_drivers
>>>
>>> What is 'python native'?  psycopg works as long you update your libpq.
>>
>> I thought pythonistas preferred using a native driver that didn't use
>> libpq ?
>
> Anyway, I would think psycopg would be classed as a native binding, as it
> uses libpq which is 'native' to PostgreSQL.

"Native" in the context of a Python library refers to a library
written in pure Python, which can run with any implementation of the
Python language: in C (CPython, the canonical implementation), in Java
(JPython), in .NET (IronPython), in Python (PyPy)... As such psycopg2
is not a native library because it's written in C and designed only
for CPython.

Notice that "native" and "using the libpq" are not mutually exclusive:
using FFI it is possible to load dynamically the libpq and have pure
Python code calling into the libpq, without the need to build a Python
C extension. This is the case of psycopg2-ctypes and psycopg2cffi,
which are drop-in replacements for psycopg2 (I contributed to the
-ctypes variant at the time and made sure of its interface by dropping
in the entire psycopg2 test suite and hammering it until all tests
passed; -cffi forked from it). Both projects feature-wise seem stuck
to psycopg 2.5 era (circa 2012); psycopg2-ctypes seems unmaintained,
psycopg2cffi seems still maintained instead, with a release a few
months ago (unfortunately with a confusing version number). These
projects are inherently slower than the C psycopg2, which performs
more operations in C, but because PyPy has a JIT is roughly makes up
for the speed lost implementing certain inner loops in Python.

Another way to be Python-native is to do without libpq and to parse
the client-server protocol in python. Of course this misses new
features, encryption schemas, and requires the reimplementation in
python of a lot of subtleties (env vars, service files...) that a
libpq-binding solution has for free. The most known example of such
driver is pg8000, which is barely used and barely maintained (had a
release in recent times after a long lull).

In terms of what the industry uses, ISTM almost everyone uses the
psycopg2 with CPython, with psycopg2cffi used by PyPy users, which
apparently do ok without the new features introduced in following
psycopg versions (replication, SQL composition...). Apart from
CPython, targeting PyPy is the only meaningful goal as JPython and
IronPython are not so used anymore. pg8000 and other drivers are just
not very used.

> If someone has greater knowledge here, it would be nice to update the wiki
> pages with new info.

I've added a link to psycopg2cffi. I don't think mentioning other
drivers is really useful for concrete and current use cases.


-- Daniele


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

Re: [GENERAL] [HACKERS] SSL and Encryption

[Daniele Varrazzo]

On Fri, Nov 3, 2017 at 5:22 AM, John R Pierce  wrote:
> On 11/2/2017 10:12 PM, Jeff Janes wrote:
>>
>> https://wiki.postgresql.org/wiki/List_of_drivers
>>
>> What is 'python native'?  psycopg works as long you update your libpq.
>
> I thought pythonistas preferred using a native driver that didn't use libpq

I'm pretty sure they don't. The industry standard uses libpq.

-- Daniele


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

Re: [GENERAL] Function not inserting rows

[Daniele Varrazzo]

On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster  wrote:

> Any ideas ?

commit?

-- Daniele


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

[GENERAL] upsert: is there a shortcut?

[Daniele Varrazzo]

Hello,

it seems not, but I feel like asking.

Is there a way to express a statement like "if you have a conflict on
insert replace all the values in the conflicting record" without
specifying all the fields explicitly? I.e. in a replication system
(where occasional accidents mean replication may restart slightly
before what's already on the target) I generate statements like:

insert into "order_log" ("id","cr_date","order_id","message")
values (%s, %s, %s, %s)
on conflict ("id") do update
set ("cr_date","order_id","message") =
(excluded."cr_date",excluded."order_id",excluded."message")

Is there a way to avoid replicating the list of fields and use instead
something like (new.*) = (excluded.*) as one could do in a trigger?
(that would also imply an (id = excluded.id but it seems harmless).

It seems to me an use case common enough that some syntactic help...
would help. "do update *"? "do update (target.*) = (excluded.*)"?


-- Daniele


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

[GENERAL] Missing space in message

[Daniele Varrazzo]

Patch attached.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 595a609..c8c4eed 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2461,7 +2461,7 @@ static struct config_int ConfigureNamesInt[] =
 
 	{
 		{wal_retrieve_retry_interval, PGC_SIGHUP, REPLICATION_STANDBY,
-			gettext_noop(Sets the time to wait before retrying to retrieve WAL
+			gettext_noop(Sets the time to wait before retrying to retrieve WAL 
 		 after a failed attempt.),
 			NULL,
 			GUC_UNIT_MS

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

Re: [GENERAL] Missing space in message

[Daniele Varrazzo]

On Tue, Jul 7, 2015 at 12:08 AM, David Rowley
david.row...@2ndquadrant.com wrote:
 On 7 July 2015 at 10:52, Daniele Varrazzo daniele.varra...@gmail.com
 wrote:

 Patch attached.



 Thanks for the patch.

 Would you be able to post it to pgsql-hack...@postgresql.org instead?

Oops, sorry. Got this wrong, for the other errors I've found I've
already sent the patches to -hackers.

-- Daniele


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

Re: [GENERAL] which Update quicker

[Daniele Varrazzo]

On Tue, Sep 23, 2014 at 8:35 PM, Emi Lu em...@encs.concordia.ca wrote:
 Hello list,

 For a big table with more than 1,000,000 records, may I know which update is
 quicker please?

 (1) update t1
   set c1 = a.c1
   from a
   where pk and
  t1.c1a.c1;
  ..
   update t1
   set c_N = a.c_N
   from a
   where pk and
  t1.c_Na.c_N;


 (2)  update t1
   set c1 = a.c1 ,
 c2  = a.c2,
 ...
 c_N = a.c_N
  from a
  where pk AND
(  t1.c1  a.c1 OR t1.c2  a.c2. t1.c_N  a.c_N)

Definitely the second, and it produces less bloat too.


 Or other quicker way for update action?

You may express the comparison as (t1.c1, t1.c2, ... t1.cN)  (t2.c1,
t2.c2, ... t2.cN)
It's not going to be faster but maybe it's more readable.


-- Daniele


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

[GENERAL] Sequences in foreign tables

[Daniele Varrazzo]

Hello,

I'm learning now something about foreign tables in PG 9.3. I wonder if
there is a clean way to use a sequence on the remote side, so that an
insert into remote_table values ([data not including id]) returning
id would ask the remote server to generate a new value for id.

As it stands now defaults are evaluated client-side and a column with
no default is treated as defaulting to null (as per docs): I see from
the logs that explicit nulls are sent to the server even if not
requested by the insert, or if default is specified.

I've worked around that using a trigger on the remote table to
reinstate the defaults, something along the line of:

create or replace function ... returning trigger
begin
   if new.id is null then new.id = nextval('seqname'::regclass); end if;
   if new.cr_date is null then new.cr_date = now(); end if;
   return new;
end

create trigger ... before insert for each row...

but I wonder if there is a more idiomatic way to do that. Using a
sequence on the remote side instead of the local seems a basic use
case and local sequences are not an option if more than one database
have a foreign table on the same physical table.

Thank you very much

-- Daniele


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

Re: [GENERAL] Sequences in foreign tables

[Daniele Varrazzo]

On Tue, Sep 16, 2014 at 6:04 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
 On Tue, Sep 16, 2014 at 8:05 AM, Daniele Varrazzo
 daniele.varra...@gmail.com wrote:

 I'm learning now something about foreign tables in PG 9.3. I wonder if
 there is a clean way to use a sequence on the remote side, so that an
 insert into remote_table values ([data not including id]) returning
 id would ask the remote server to generate a new value for id.

 You could always define foreign table on local node without the
 columns having default values you want to enforce on remote side, and
 you may even be able to do well with such a definition on local side
 as it does not seem you want to make the default remotes visible on
 local side

Well, actually I do: see the query in question. The returning id
requires the field id to exist on the foreign table :)

 (always possible to use an extra foreign table definition
 btw). So for example:

Yes, of course, but I'm experimenting into how transparent would be to
replace the table with a foreign table and leave some working code
unchanged (it works fine with the trigger workaround). By the way even
if I had a table without id where to write to and one with the id to
read from doesn't help in detecting what unique id was generated
remotely.

-- Daniele


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

Re: [GENERAL] How to insert either a value or the column default?

[Daniele Varrazzo]

On Mon, Aug 25, 2014 at 3:26 PM, Adrian Klaver
adrian.kla...@aklaver.com wrote:
 On 08/25/2014 06:32 AM, W. Matthew Wilson wrote:

 First of all, thanks for showing this trick!

 But I am confused.  Will this trick stop working in a future version
 of psycopg2?  Should I avoid using it?


 Here is the email laying out the issues:

 http://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=ah...@mail.gmail.com

That's correct, thank you Adrian.

Matthew: no, this will correctly work in all the future psycopg2
versions. Even if we started supporting a new protocol, such as the
ISQLParam referred in the mentioned email, it won't be the default in
psycopg2 and it should be enabled on purpose.

The new protocol should be the default in this mythical psycopg3
instead. If something can be imported as psycopg2 it will support the
ISQLQuote protocol by default, hence the Default object as implemented
in this thread will work.

-- Daniele


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

Re: [GENERAL] How to insert either a value or the column default?

[Daniele Varrazzo]

On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson m...@tplus1.com wrote:
 I have a table that looks sort of like this:

 create table tasks
 (
 task_id serial primary key,
 title text,
 status text not null default 'planned'
 );

 In python, I have a function like this:

 def insert_task(title, status=None):
 

 and when status is passed in, I want to run a SQL insert statement like this:

 insert into tasks
 (title, status)
 values
 (%s, %s)

 but when status is not passed in, I want to run this SQL insert instead:

 insert into tasks
 (title, status)
 values
 (%s, default)

You can easily do that in psycopg with:

class Default(object):
def __conform__(self, proto):
if proto is psycopg2.extensions.ISQLQuote:
return self
def getquoted(self):
return 'DEFAULT'

DEFAULT = Default()

 print cur.mogrify('insert into place values (%s, %s)',
['adsf', DEFAULT])
insert into place values ('adsf', DEFAULT)

You can find more details at
http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax

It should be added to the library (it was first discussed in 2003...),
but it's one of these things that will stop working when psycopg will
start using the extended query protocol (together with other nifty
features such as string literals for table/columns names) so in my
mind it can only be included when psycopg will be able to do both
client-side parameter interpolation and server-side arguments passing,
and when the distinction between the two strategies will be clear
(this is planned for a future psycopg3 but there is no timeline for it
yet).

-- Daniele


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

[GENERAL] Constraint exclusion on tables partitioned over range types

[Daniele Varrazzo]

Hello,

we are setting up a partitioned table based on tstzrange in PG 9.3,
something like:

create table offer (
   during tstzrange not null,
   ...
   constraint virtual check (false) no inherit
   );

create table offer_201408 (
check (during @ '[2014-08-01Z,2014-09-01Z)'::tstzrange)
) inherits (offer);

create table offer_201409 (
check (during @ '[2014-09-01Z,2014-10-01Z)'::tstzrange)
) inherits (offer);

I haven't found a way to make the planner constraint exclusion kicking in:

=# explain select * from offer where during @ '2014-08-03'::timestamptz;

Append  (cost=0.00..27.25 rows=3 width=248)
   -  Seq Scan on offer  (cost=0.00..0.00 rows=1 width=248)
 Filter: (during @ '2014-08-03 00:00:00+01'::timestamp
with time zone)
  -  Seq Scan on offer_201408  (cost=0.00..13.62 rows=1 width=248)
Filter: (during @ '2014-08-03 00:00:00+01'::timestamp
with time zone)
  -  Seq Scan on offer_201409  (cost=0.00..13.62 rows=1 width=248)
Filter: (during @ '2014-08-03 00:00:00+01'::timestamp
with time zone)

Similar results using tztzrange OP tstzrange operators with OP in , @, @.

Seqscans aside, as these tables are empty so they are expected, I
wonder if there is a way to organize the operators used in the
constraints and the ones used in the query so that the query planner
would be able to exclude some of the tables before querying them, as
is easy to do implementing range constraints on the base tstz type and
its ordering operators.

It would be also nice if the always failing constraint on the base
table could suggest the planner that there is no record to be found
there: I think this would be easier to implement but not as useful as
for the ranges.

Thank you very much,

-- Daniele


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

[GENERAL] OIDs for jsonb type

[Daniele Varrazzo]

Hello,

I'm going to add support to the jsonb data type in psycopg2, in order
to have the type behaving like json currently does
(http://initd.org/psycopg/docs/extras.html#json-adaptation).

Is it correct that oid and arrayoid for the type will be 3802 and 3807
and that they won't change before the 9.4 final release?

Thank you very much

-- Daniele


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

Re: [GENERAL] Handling of tz-aware literals in non-tz-aware fields

[Daniele Varrazzo]

On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
 PostgreSQL never examines the content of a literal string before determining
 its type, and therefore will treat both of the above as timestamp without
 time zone. To ensure that a literal is treated as timestamp with time zone,
 give it the correct explicit type:

 TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

 In a literal that has been determined to be timestamp without time zone,
 PostgreSQL will silently ignore any time zone indication. That is, the
 resulting value is derived from the date/time fields in the input value, and
 is not adjusted for time zone.

Perfect. Well, probably less than perfect but perfectly documented.

A psycopg user was surprised because a Python datetime with time zone
is erroneously treated correctly in a query from Python (i.e. the
tzinfo is not discarded but taken in consideration)
http://psycopg.lighthouseapp.com/projects/62710/tickets/176. This is
because psycopg implicitly adds a timestamptz cast.

The only thing that bothers me is that in a future psycopg
implementation we may drop the cast, only relying on postgres
behaviour, and as a consequence passing a tz-aware object to a
non-tz-aware field (which is an operation that smells like
underspecified anyway) may change result. Such version would be
not-backward-compatible for other reasons, so it doesn't bother me
excessively.

-- Daniele


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

[GENERAL] Handling of tz-aware literals in non-tz-aware fields

[Daniele Varrazzo]

Hello,

the issue can be show with this example:

piro= SET TIMEZONE = 'America/New_York';
SET

piro= select '1970-01-01T00:00:00+03:00'::timestamp;
  timestamp
-
 1970-01-01 00:00:00
(1 row)

piro= select '1970-01-01T00:00:00+03:00'::timestamptz::timestamp;
  timestamp
-
 1969-12-31 16:00:00
(1 row)

I find surprising that an unknown literal containing a TZ-aware
timestamp has the tz info discarded (e.g. upon insertion in a
timestamp without time zone field), whereas the cast from tz-aware to
non-tz-aware performs a conversion. I find the second behaviour much
more reasonable.

Is there an explanation for the first behaviour?

Is the first behaviour documented?

Thank you very much,


-- Daniele


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

Re: [GENERAL] Selecting timestamp from Database

[Daniele Varrazzo]

On Mon, Apr 8, 2013 at 3:15 PM, Richard Harley rich...@scholarpack.com wrote:

 That returns nothings also. But I have spied the problem now:

 select  ATTENDANCE.timestamp::text from attendance order by timestamp desc
 limit 1

 return the actual timestamp: 2013-04-08 12:42:40.089952

 So the theory I'm wondering about is that the stored data in fact
 contains (some values with) fractional seconds, but Richard's
 client-side software isn't bothering to show those, misleading him
 into entering values that don't actually match the stored data.
 Looking at the table directly with psql would prove it one way
 or the other.

 This is it. It was the psycopg adapter. My bad!!

This message can be misread as psycopg dropping the fractional part of
the timestamp, which is not the case:

 cur.execute(select '2013-04-08 12:42:40.089952'::timestamp)
 cur.fetchone()[0]
datetime.datetime(2013, 4, 8, 12, 42, 40, 89952)

Just FYI.

-- Daniele


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

Re: [GENERAL] libpq compatibility

[Daniele Varrazzo]

On Tue, Feb 12, 2013 at 10:19 AM, Philipp Kraus
philipp.kr...@flashpixx.de wrote:
 Hello,

 I'm using PG 9.2.2 with Qt for accessing the server. If I compile Qt with the 
 9.2.2 libpg, can I use this libpg also with eg 8.3 ?
 So can I use the libpg with earlier Postgres database versions?

Yes, you can use the most recent libpq to talk with fairly old
PostgreSQL versions.

-- Daniele


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

Re: [GENERAL] Question about PARTIAL DATE type/s

[Daniele Varrazzo]

On Sun, Oct 7, 2012 at 12:28 PM, r d rd0...@gmail.com wrote:
 Hi,

 I have text fields which contains dates in the format 'MM' (four
 positions for the year, two for the month).
 These fields are contained in text files which I load into the DB.

 When I convert this field to a date using

 to_date(PARTIAL_DATE_FIELD,'MM'); -- (a cast won't recognize
 the input as valid)

 I still get full dates as an output, for example,
 '198801' --- 1988-01-01
 '196408' --- 1964-08-01
 and so on, what is wrong in this case because nowhere it is said that I have
 the /first/ of that month, the entry just says that the event in question
 happened /during/ that month.


 My question:
 Is it possible to define fields which contain partial dates per above? I
 found nothing in the manual.

There is no partial date type. You can use a dates range to
represent explicitly what you mean.

http://www.postgresql.org/docs/9.2/static/rangetypes.html

e.g. this function returns the range requested:

postgres=# create function partial_month(s text)
returns daterange
language sql
as $$
select daterange(
to_date($1, 'MM'),
to_date(($1::int + 1)::text, 'MM'),
'[)');
$$;
CREATE FUNCTION

postgres=# select partial_month('201202');
  partial_month
-
 [2012-02-01,2012-03-01)
(1 row)

postgres=# select partial_month('201212');
  partial_month
-
 [2012-12-01,2013-01-01)
(1 row)

Note: it exploits to_date() parsing '200013' as '2001-01', which is
reasonable but haven't found documented and don't know how much
reliable. Writing a safer one month later function is left as
exercise.

-- Daniele


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

Re: [GENERAL] Question about PARTIAL DATE type/s

[Daniele Varrazzo]

On Sun, Oct 7, 2012 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Daniele Varrazzo daniele.varra...@gmail.com writes:
 Note: it exploits to_date() parsing '200013' as '2001-01', which is
 reasonable but haven't found documented and don't know how much
 reliable. Writing a safer one month later function is left as
 exercise.

 Consider adding '1 month'::interval to the month start date.

 (This function relies on text-munging way too much for my taste.
 There's almost always a better way to do it than that.)

Didn't realize intervals store months/days info separately: I thought
an interval was just a vector in the timestamp space. Nice surprise.

-- Daniele


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

[GENERAL] Python mapping with new 9.2 data types

[Daniele Varrazzo]

Hello,

I've worked at Python-Postgres mapping for the new PostgreSQL 9.2 data
types. They should be released with the next psycopg2 version.

The current design, susceptible to changes if needed, is documented here:

- JSON adaptation: http://initd.org/psycopg/docs/extras.html#json-adaptation
- Range data types: http://initd.org/psycopg/docs/extras.html#range-data-types

The code is available in feature branches of my github repository:
https://github.com/dvarrazzo/psycopg

Any feedback (tests, comments, docs corrections) is very welcome.

Regards,

-- Daniele


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

Re: [GENERAL] Running CREATE only on certain Postgres versions

[Daniele Varrazzo]

On Mon, Sep 24, 2012 at 2:32 PM, Robert James srobertja...@gmail.com wrote:
 I have some code which creates a function in Postgres, taken from
 http://wiki.postgresql.org/wiki/Array_agg .

 DROP AGGREGATE IF EXISTS array_agg(anyelement);
 CREATE AGGREGATE array_agg(anyelement) (
 SFUNC=array_append,
 STYPE=anyarray,
 INITCOND='{}'
 );

 The function was added in 8.4, and so the code fails when run on 8.4 or 
 higher.

 How can I make the code cross-version compatible? For instance, how
 can I tell it to check the version, and only run if 8.3 or lower?   Or
 another way to make it cross-version?

You could create a plpgsql function that tries to creates the object
catching the exception, then call the function and drop it. Something
like the following (untested):

create function try_to_create_aggregate() language plpgsql as $$
begin
begin
execute $agg$
DROP AGGREGATE IF EXISTS array_agg(anyelement);
CREATE AGGREGATE array_agg(anyelement) (
...
$agg$
exception
see here to know how to handle

http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
end;
end
$$;

select try_to_create_aggregate();

drop function try_to_create_aggregate();

In more recent postgres versions you can use do avoiding to create
the function.

-- Daniele


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

Re: [GENERAL] bgwriter and pg_locks

[Daniele Varrazzo]

On Wed, Sep 12, 2012 at 8:27 PM, Ben Chobot be...@silentmedia.com wrote:
 In an attempt to get a hackfix for 
 http://pgfoundry.org/tracker/index.php?func=detailaid=1011203group_id=1000411atid=1376,
  I'm wonder if it's true that, when looking at pg_locks, the only pid which 
 will have virtualxid = '1/1' and virtualtransaction = '-1/0' will be the 
 bgwriter. That seems correct on all my dbs, but that's hardly a definitive 
 test.

Just stumbled upon the same issue. You got no reply here... maybe it's
worth contacting -hackers about it. I've tried googling around and
found no discussion about the bgwriter keeping that lock, except your
message.

Good luck, cheers!

-- Daniele


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

[GENERAL] Handling error during async query

[Daniele Varrazzo]

Hello,

I'm trying to fix psycopg2 issue #113: network disconnection not
handled correctly in async mode.

If I'm in the middle between the PQsendQuery and the PQgetResult and
an error is detected (let's say we don't know yet if
application-related or connection-related), is there a way to abort
the query and put it back in a status where PQsendQuery can be
executed again (I believe transaction status from ACTIVE to IDLE)
without fetching the result?

Psycopg currently tries to fetch (blocking) and discard the result
after an error:

while (NULL != (res = PQgetResult(conn-pgconn))) {
PQclear(res);
}

but if the network connection has gone down it will result in waiting
forever. Not to mention the fact the operation is blocking when the
user wanted nonblocking operations.


-- Daniele

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

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

[Daniele Varrazzo]

On Mon, Jul 16, 2012 at 3:56 PM, Craig Ringer ring...@ringerc.id.au wrote:
 On 07/16/2012 07:41 PM, Alban Hertroys wrote:

 BTW, that second value looks a whole lot like a poorly thought out

 Yup. The 'infinity' value doesn't play well with all database access APIs
 and languages, though.

It doesn't even play well with PostgreSQL's extract(). I reported it
some times ago but as of 9.1.4 it has not been fixed.

=# select extract(epoch from 'infinity'::timestamp);
 date_part
---
 0

This makes 'infinity' a problematic choice in any application
requiring a mapping between dates and reals, such as when using
intervals in gist indexes.

-- Daniele

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

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

[Daniele Varrazzo]

On Mon, Jul 16, 2012 at 7:08 PM, David Johnston pol...@yahoo.com wrote:

 Given:
 A) extract() returns a double precision
 B) ... In addition to ordinary numeric values, the floating-point types
 have several special values: ... 'Infinity ...

 I would vote that the epoch result should be Infinity.

That's what I think the only reasonable value if extract(epoch) was to
be used to map timestamps to doubles: the order relation is preserved,
hence indexes work as they should. Then, of course, I may be asking
too much from that function (although in that case the need for a
purposely designed mapping function would be felt. At least, I've felt
it).


 Specific, but unknown (e.g., day of week, month, year, etc...) results could
 return NaN though NULL is also, probably more, reasonable given the
 context.

 The goal would be to use Infinity in case where  comparisons are
 common and use NULL where = comparisons are common.

 Personally I'd prefer to generate an error in places where NULL would be
 the result in order to minimize bugs.

The use case of extracting anything else than epoch from infinity is
not so compelling. And of course the result is undetermined. Having to
choose one, I'd go for the same result of sin(inf), which is NaN.

Even without this quirk, the problem of mapping timestamps to other
languages data types could be an even stronger design factor. I've
personally settled for -12-31 which is python's datetime.max, maps
ok to doubles and won't create problems for almost 8000 years.


-- Daniele

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

Re: [GENERAL] Can't figure out how to use now() in default for tsrange column (PG 9.2)

[Daniele Varrazzo]

On Mon, Jul 16, 2012 at 10:30 PM, Chris Bartlett
c.bartl...@paradise.net.nz wrote:
 At 8:35 PM +0100 16/7/12, Daniele Varrazzo wrote:

 Even without this quirk, the problem of mapping timestamps to other
 languages data types could be an even stronger design factor. I've
 personally settled for -12-31 which is python's datetime.max, maps
 ok to doubles and won't create problems for almost 8000 years.

 Stephane Faroult ('The Art of SQL') says that dates so far in the future can
 throw off query planners. He explains it nicely here:
 http://www.youtube.com/watch?v=gu0WJJXgEFM

Nice article. It made me worry about what I've put into my db... So
I've made a test: I've tried creating a table with about 100K records
uniformly spread across one year:

= create table datetest (ts timestamp);
= insert into datetest (ts) select
generate_series('2012-1-1'::timestamp, '2012-12-31'::timestamp, '5
minutes'::interval) ;
INSERT 0 105121
= analyze datetest ;
= select * from pg_stats where tablename = 'datetest' and attname = 'ts';

you get a nice uniform histogram. I've tried screwing it up inserting
10, 100, 1000, 10K, 100K infinity and/or -12-31: the histogram
doesn't move of an inch (the values get recorded in most_common_vals
but not in histogram_bounds).

This already confirms that postgres doesn't suffer of the problem
highlighted in the article. I've made another test, adding first 365,
then about 10K distinct values in the year 3000-3001 range:

= insert into datetest (ts) select
generate_series('3000-1-1'::timestamp, '3000-12-31'::timestamp, '1
day'::interval) ;
INSERT 0 365
= analyze datetest ;
= select * from pg_stats where tablename = 'datetest' and attname = 'ts';

= insert into datetest (ts) select
generate_series('3000-1-1'::timestamp, '3000-12-31'::timestamp, '1
hour'::interval) ;
INSERT 0 8737
= analyze datetest ;
= select * from pg_stats where tablename = 'datetest' and attname = 'ts';

and it moves... of an inch. In the first case only one bin (of the 100
PG 9.1 defaults) contains dates around the 3000, in the second case
only about 8. And the values around the Y3K are in fact the 8% of the
table. No bin is wasted to record data between the 2013 and the 3000.
This suggests me that the PG histogram doesn't count the occurrence
into bins of the same size over the domain, but instead records the
boundaries on the domain of bins containing the same amount of data.

Pretty solid!

-- Daniele

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

[GENERAL] Reading storage parameters

[Daniele Varrazzo]

Hello,

is there a way to read the storage parameters values set on a table
(i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and
so on...)? I can't find it in the docs.

-- Daniele

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

Re: [GENERAL] Reading storage parameters

[Daniele Varrazzo]

On Thu, Jun 21, 2012 at 1:26 PM, Thom Brown t...@linux.com wrote:
 On 21 June 2012 13:12, Daniele Varrazzo daniele.varra...@gmail.com wrote:
 Hello,

 is there a way to read the storage parameters values set on a table
 (i.e. what set with ALTER TABLE foo SET (autovacuum_enabled=false) and
 so on...)? I can't find it in the docs.

 SELECT c.reloptions
 FROM pg_class c
 INNER JOIN pg_namespace n
  ON c.relnamespace = n.oid
 WHERE c.relname = 'tablename'
 AND n.nspname = 'schemaname';

Ok, so they are in pg_class.reloptions, thank you!

-- Daniele

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

Re: [GENERAL] rounding a timestamp to nearest x seconds

[Daniele Varrazzo]

On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson a...@squeakycode.net wrote:
 Took me a while to figure this out, thought I'd paste it here for others to
 use:

 create or replace function round_timestamp(timestamp, integer) returns
 timestamp as $$
        select date_trunc('minute', $1) + cast(round(date_part('seconds',
 $1)/$2)*$2 || ' seconds' as interval);
 $$ language sql immutable;


 If you pass 10 to the second argument, it'll round the timestamp to the
 nearest 10 seconds.  Pass 5 to round to nearest 5 seconds, etc..

Your function can only round the seconds: it cannot round on intervals
longer than one minute and always rounds down to the minute, creating
irregular intervals, e.g.:

= select round_timestamp('2012-03-12 01:42:58', 13);
 2012-03-12 01:42:52
= select round_timestamp('2012-03-12 01:42:59', 13);
 2012-03-12 01:43:05
= select round_timestamp('2012-03-12 01:43:00', 13);
 2012-03-12 01:43:00

You don't get discontinuities if you map the timestamp on the real
axis by extracting the epoch, play there and then go back into the
time domain:

create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
select 'epoch'::timestamp + '1 second'::interval * ($2 *
round(date_part('epoch', $1) / $2));
$$ language sql immutable;

This version can round on any interval specified in seconds (but it
would be easy to specify the step as interval: date_part('epoch',
interval) returns the interval length in seconds).

-- Daniele

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

Re: [GENERAL] How to get a signal from the database when a INSERT INTO is done?

[Daniele Varrazzo]

On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp ma...@juffo.org wrote:
 On Tue, Feb 28, 2012 at 13:07, Andre Lopes lopes80an...@gmail.com wrote:
 This is a mixed question Python/PostgreSQL... I need to get a signal
 in my python application when a new insert is done. How can this be
 done, any clues?

 As depesz mentioned, you can use the LISTEN and NOTIFY commands for
 this asynchronous signalling. You can add an after-insert trigger to
 send the NOTIFY.

 On the Python end, you have to call psycopg2 connection.poll() method
 periodically or in response to select() activation, and check the
 connection.notifies list. Note that this list can grow even when you
 run other queries -- not just on poll.

 There's an example here:
 http://initd.org/psycopg/docs/advanced.html#async-notify

Using an asynchronous IO-driven framework such as eventlet or gevent
you don't even need to poll the connection to look for events: you
just get notified as soon as there is something to read. Here is an
example:

http://initd.org/psycopg/articles/2010/12/01/postgresql-notifications-psycopg2-eventlet/

It's enough to have a NOTIFY executed in a trigger on insert, maybe
putting the id in the notify payload, and you get timely notifications
about inserts in Python world.

You may be able to do something similar in Twisted using txPostgres:
when I asked Jan about that, IIRC he said they weren't ready yet, but
it was several months ago, it may have changed since then.

-- Daniele

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

Re: [GENERAL] How to get a signal from the database when a INSERT INTO is done?

[Daniele Varrazzo]

On Tue, Feb 28, 2012 at 3:45 PM, Marti Raudsepp ma...@juffo.org wrote:
 On Tue, Feb 28, 2012 at 17:41, Daniele Varrazzo
 daniele.varra...@gmail.com wrote:
 On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp ma...@juffo.org wrote:
 On the Python end, you have to call psycopg2 connection.poll() method
 periodically or in response to select() activation
 There's an example here:
 http://initd.org/psycopg/docs/advanced.html#async-notify

 Using an asynchronous IO-driven framework such as eventlet or gevent
 you don't even need to poll the connection to look for events

 As mentioned above and as demonstrated in the example, select() also
 does the job. Using such a fancy framework is usually an overkill.

Yeah, the problem is usually if you have to do something else apart
from listening from the notification. select() will block the entire
application, so you would put it into a separate thread to have the
app running on. From here to start benefiting from eventlet the step
is very short. If a blocking behaviour is ok, then no problem using
bare select().

-- Daniele

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

Re: [GENERAL] python3 module for accessing postgresql

[Daniele Varrazzo]

On Wed, Feb 22, 2012 at 11:10 AM, Andrew Gould
andrewlylego...@gmail.com wrote:
 Does anyone have any advice regarding the selection of a Python 3
 module for PostgreSQL?  The modules I've found so far are psycopg2 and
 py-postgresql.  My skill level with Python is fairly basic.

Psycopg works perfectly on Python 3 and is the most used module on
Python 2, making a straightforward upgrade path.

It's probably not packaged yet by distributions, but it's fairly easy
to compile it from source:

python3 setup.py build
sudo python3 setup.py install

You will need the libpq and the python-dev packages installed. If
pg_config is not found in standard locations, building will fail with
a clean description of the problem and how to specify the program
location.

If you have any problem, you may get further help on the psycopg mailing list.

Cheers,

-- Daniele

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

Re: [GENERAL] Duplicated entries are not ignored even if a do instead nothing rule is added.

[Daniele Varrazzo]

On Wed, Jan 4, 2012 at 8:09 PM, Alban Hertroys haram...@gmail.com wrote:

 On 4 Jan 2012, at 3:03, 邓尧 wrote:

 True, I don't need transactions, neither do I want them, but psycopg2 create 
 transactions for me automatically :-(

 Well, if psycopg didn't, Postgres would wrap each statement in a transaction 
 anyway. But that doesn't matter.

FYI, you can have psycopg in autocommit mode if you need that:
http://initd.org/psycopg/docs/connection.html#connection.autocommit.

-- Daniele

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

[GENERAL] Using a domain

[Daniele Varrazzo]

Hello,

I'm trying to use a domain to define a data type constraint, let's say
an hypothetical uk_post_code with pattern LNNLL. I'd enforce no
whitespaces, all uppercase.

I would also need a way to normalize before validate: given an input
such as w3 6bq, normalize it to W36BQ before trying to apply the
check. It would be great if I could give this function the same name
of the domain, so that uk_post_code('w3 6bq') would return W36BQ cast
to the domain.

Unfortunately it seems a domain implicitly defines a function, and
this function only perform the cast: the above is thus equivalent to
'w3 6bq'::uk_post_code, which would fail as the constraint doesn't
match. IIRC from when I've played with type definitions in C, for a
type there is no such automatic definition: a function converting text
to the type must be explicitly provided. \df doesn't show such
function for the domain (nor DROP FUNCTION seems knowing it), and if I
create one, it is not invoked (the cast takes precedence).

Is there any way to define a conversion in a function call
uk_post_code(text), or the only way to provide a normalization
function is to give it a different name (such as to_uk_post_code - I'd
like to know if there is a convention in how to name this function).

Is there any documentation about domains apart from the
CREATE/ALTER/DELETE commands? Haven't found any in the docs.

Thanks.

-- Daniele

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

Re: [GENERAL] missing chunk 0 for toast value ...

[Daniele Varrazzo]

On Wed, Oct 26, 2011 at 1:04 AM, Andrew Hammond
andrew.george.hamm...@gmail.com wrote:
 On Tue, Oct 25, 2011 at 4:53 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 [ pokes around for a bit ... ]  Hmm, can you say how the failing query
 was submitted, exactly?  I'm wondering if it came in via simple Query
 (PQexec) or extended-query protocol (anything with parameters).

 The command was sent from a python via django-1.2.1 using psycopg2-2.2.1

PQexec then.

-- Daniele

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

Re: [GENERAL] How to find freak UTF-8 character?

[Daniele Varrazzo]

On Sat, Oct 1, 2011 at 10:16 PM, Leif Biberg Kristensen
l...@solumslekt.org wrote:
 Yes I know that this is a perfectly legal UTF-8
 character. It crept into my database as a result of a copy-and-paste job from
 a web site. The point is that it doesn't have a counterpart in ISO-8859-1 to
 which I regularly have to export the data.

If your database is utf8 and the output latin1, it sounds more robust
to connect to the database with utf8 client encoding, pull data from
the database as unicode and at output time use data.encode('latin1',
'replace') in python to convert data without bombing on non-latin1
chars.

-- Daniele

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

Re: [GENERAL] Anonymous record type and inner types.

[Daniele Varrazzo]

On Fri, Jul 1, 2011 at 9:37 AM, Ronan Dunklau rdunk...@gmail.com wrote:

 When I execute a statement such as Select (an_int, a_varchar) as a_record
 from test, the type returned for a_record is record (oid 2249).

 Is the inner type information propagated somehow (that an_int is an integer,
 and a_varchar a varchar) ?

 If so, is it easily accessible in client libraries ? I'm using psycopg2 for
 python, which itself uses libpq.

I don't think the information about the components of the composite
type are automatically propagated to the client nor made accessible by
the libpq.

You may create an user-defined type representing the record you select
and cast the result to it: for example if you create type thing as
(the_int integer, the_text varchar); and then select (an_int,
a_varchar)::thing from test, postgres will pass the thing oid to
the client, instead of the one of the generic record. psycopg can be
programmed to return a tuple/namedtuple allowing you to get the
components as python values: see
http://initd.org/psycopg/docs/extras.html#composite-types-casting
for further details.

-- Daniele

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

[GENERAL] psql and query buffer mangling

[Daniele Varrazzo]

Hello,

I see psql performs some elaboration on the input query before storing
it in the query buffer: for instance variables are replaced:

=# \set test 10
=# select :test;
 ?column?
--
   10
(1 row)

=# \p
select 10;

and comments are stripped:

duma=# select 1; -- comment
 ?column?
--
1
(1 row)

duma=# \p
select 1;

Sometimes I use psql to test complex queries which are part of
programs, thus containing placeholders: roundtripping through the
editor via \e will lose the placeholders (as well as the comments)
making harder to test different values and finally to paste back the
query into the program.

Wouldn't be a better behaviour to store the unmodified input into the
query buffer and to perform the required query mangling downstream?

-- Daniele

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

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

[Daniele Varrazzo]

On Thu, Apr 28, 2011 at 7:26 PM, Joshua D. Drake j...@commandprompt.com wrote:

 Well there is no fact to back that up but, I will say that most toolkits
 require the use of a synthetic key, rails, django etc

Usually such tools are born with surrogate keys only, because it's
easier, and either grow up developing natural keys (e.g. the Django
ORM, SQLAlchemy) or fade into uselessness (e.g. SQLObject). But this
speaks more about tools than about the merits of the natural keys: if
the toolkit doesn't support them it's seriously getting in the way in
this and probably in other matters too.

-- Daniele

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

Re: [GENERAL] setting connection/ query timeout

[Daniele Varrazzo]

On Tue, Apr 19, 2011 at 8:09 PM, tamanna madaan
tamanna.ma...@globallogic.com wrote:

 Sometimes this query gets stuck for unknown reason . So, for this particular
 query I want to set a timeout
 . I dont want to change statement_timeout in postgresql.conf as this would
 affect all the connections/queries.
 I just want to set timeout for above mentioned query . How can I do that ??

You can set a timeout only valid for the session using SET:

= set statement_timeout to 1000;
SET
= select pg_sleep(2);
ERROR:  canceling statement due to statement timeout

-- Daniele

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

[GENERAL] Non-storable data type

[Daniele Varrazzo]

Hello,

writing an extension library, I have a type only used to perform
efficient in-place aggregation, but absolutely not to be used as a
data type into a table (it contains pointers, so it would be a
guaranteed crash).

Is there a way to mark the type as non-storable?

Thanks,

-- Daniele

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

Re: [GENERAL] Non-storable data type

[Daniele Varrazzo]

On Sat, Mar 26, 2011 at 10:35 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Daniele Varrazzo daniele.varra...@gmail.com writes:
 Hello,
 writing an extension library, I have a type only used to perform
 efficient in-place aggregation, but absolutely not to be used as a
 data type into a table (it contains pointers, so it would be a
 guaranteed crash).

 Is there a way to mark the type as non-storable?

 Can you avoid making it a type at all?  I think there are existing
 examples of aggregates that just declare their state value as INTERNAL.

I found no reference about this in the docs but yes, for instance
intagg is one of these.

However using it has not been straightforward: using the aggregate
defined with stype=internal I got the error cannot accept a value of
type internal. I've found the error is raised by internal_in: that's
because the aggregate has an initcond defined - which however is only
a dummy to work around the error received in the agg definition in
case it is omitted: must not omit initial value when transition
function is strict and transition type is not compatible with input
type

I've made internal working by redefining the transition function as
non strict: this obviously has made its code a little bit more
complex, but it's probably better than having the internal type
exposed to the sql.

Thank you.

-- Daniele

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

[GENERAL] Saving bytes in custom data type

[Daniele Varrazzo]

Hello,

I'm writing a variable size custom datatype in C. The variable part is
an array of unsigned long, and it needs to be aligned. I further need
to store a few flags, for which a single byte would be more than
enough (I would actually need just a single bit, but I'd probably keep
some bits to store a data version too).

Using a struct like

{
char vl_len_[4]; /* varlena header */
unsigned char flags;
unsigned long data[1];
}

3 bytes are always wasted in padding as offsetof(data) is 8.

I may complicate fetching a little bit and store the flags at the end
of the data, so that the total size would be 5 + data instead of 8 +
data, and access them with some pointers arithmetic.

In terms of disk space, does it worth the hassle or (as I suspect)
would this effort be wasted by on-disk alignment of the data in the
rows?

Thanks,

-- Daniele

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

Re: [GENERAL] Saving bytes in custom data type

[Daniele Varrazzo]

On Wed, Mar 16, 2011 at 6:29 PM, Merlin Moncure mmonc...@gmail.com wrote:

 question: if you are storing just flags and bytes, why not use a bytea
 and store the flags out of line?

I'm not sure I understand your question. I am writing a custom
datatype with variable size more or less following the guidelines in
http://www.postgresql.org/docs/9.0/static/xtypes.html.

More in details I am hacking at a GMP wrapper
http://pgmp.projects.postgresql.org/ and trying to store GMP
bignums, which I can re-create by just storing a variable list of
unsigned longs and a flag for the sign (note that this is not the
structure the GMP library use, but is enough data to re-create it and
one of the possible form of storage as a varlena - I'm just exploring
to decide the best one).

What do you mean for storing the flag out of line?

-- Daniele

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

Re: [GENERAL] Binary params in libpq

[Daniele Varrazzo]

On Mon, Feb 28, 2011 at 2:42 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Sun, Feb 27, 2011 at 7:39 PM, Craig Ringer

 AFAIK, the `timestamp' type moved from a floating-point to an integer
 representation internally, which would've affected the binary protocol
 representation. That was even a compile-time config option, so it could be
 different between two different Pg installs with the same version.

 Actually, this has always been a compile time option on the server as
 far as i remember and there is protocol support for it -- libpq tells
 you how it has been set...you've always had to deal with this

It should be 'integer_datetimes' setting reported by PQparameterStatus I assume.

 (libpqtypes, which I've mentioned several times as the correct way to
 'do' binary with libpq, handles this for you).  Only the default
 setting was changed.  Timestamps are the only type afaik that are like
 this so they are something of a special case.  There *was* a binary
 wire format change for timestamps some time back iirc that was
 unrelated to the int/float property.  They are unlikely to change
 again unless the server's internal representation changes.

 Small aside about timestamps: aside from bytea they stand to benefit
 the most out of all the common types by moving to binary, especially
 if you are moving them to/from something like a C struct tm on the
 client.  int, float, etc are hardly noticeable and barely worth the
 trouble.

Not only barely noticeable: I've talked with somebody who has done
some performance test and has found better performance in text mode
for small integers: apparently the time spent parsing the integer text
format was less than the overhead to pass the 8 bits of an int64 just
to represent '3'. Probably an edge case but it restates that for ints
there isn't a lot to gain from the binary format.

So it looks like we could assume the bin representation relatively
stable, but still at each postgresql major version it should be
checked if the internal representation of any data type changed. OTOH
not so many data types would really benefit from being transferred as
binary (from a quick scan to the data types list, the datetime types
look indeed the ones where there would be the most to gain).

-- Daniele

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

[GENERAL] Binary params in libpq

[Daniele Varrazzo]

Hello,

I'm thinking about adding support for PQexecParams and PQprepare in
Psycopg. I've posted more details yesterday on the Psycopg mailing
list http://archives.postgresql.org/psycopg/2011-02/msg00076.php. I
have a few preliminary questions:

How stable is the binary representation for the PostgreSQL types? We
may just pass bytea data in binary format and pass everything else as
text parameters, or pass different types too as binary, if performace
would benefit. Did binary format ever changed in the past (at least
since the introduction of the V3 protocol)? Is such binary
representation more likely to change in the future respect to the
textual one, in a way that forward/backward compatibility between
server and client would be compromised?

Query plans for prepared statements may be less efficient than ones
for queries with bound parameters. Are query plans generated for
PQexecParams calls as efficient as the ones generated for PQexec? Or
do they have the same limitation of the ones generated for PQprepare?

Thank you,

-- Daniele

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

[GENERAL] Constraint exclusion with box and integer

[Daniele Varrazzo]

Hello,

I have the typical problem of overlapping periods exclusion and I'm
trying to give constraint exclusion a go as we have recently switched
to a PG 9.0 server.

All the examples I've found check a record against the whole table.
But in practice I will have a table of resources (hotel rooms,
whatever) and independent periods for each resource. EXCLUDE allows a
list of field WITH op clauses, and I've checked that they are ANDed
so the record is rejected only in case all the checks return true, but
I don't see how to combine a check on a range with a check on the
resource id:

= alter table commission_rate add constraint check_overlapping
exclude (payer_id with =, box( point(extract(epoch from start_date),
extract(epoch from start_date)), point(coalesce(extract(epoch from
end_date), 'infinity'), coalesce(extract(epoch from
end_date),'infinity') ) ) with );
ERROR:  data type box has no default operator class for access method btree
HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

= alter table commission_rate add constraint check_overlapping
exclude using gist (payer_id with =, box( point(extract(epoch from
start_date), extract(epoch from start_date)),
point(coalesce(extract(epoch from end_date), 'infinity'),
coalesce(extract(epoch from end_date),'infinity') ) ) with );
ERROR:  data type integer has no default operator class for access method gist
HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

Can I build a constraint check using both a box (for the range) and an
integer (for a fkey) or should I go back to the classic trigger + lock
solution?

Thanks

-- Daniele

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

Re: [GENERAL] Constraint exclusion with box and integer

[Daniele Varrazzo]

On Fri, Jan 21, 2011 at 6:36 PM, Daniele Varrazzo
daniele.varra...@gmail.com wrote:

 All the examples I've found check a record against the whole table.
 But in practice I will have a table of resources (hotel rooms,
 whatever) and independent periods for each resource.

Clarification: I have read the depesz article
(http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/)
but in my problem the overlaps must be check on the combination of
three ids, so it would take a 4D box, and still I wonder if it would
be doable at all if an id was a string.

-- Daniele

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

Re: [GENERAL] Constraint exclusion with box and integer

[Daniele Varrazzo]

On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis pg...@j-davis.com wrote:
 On Fri, 2011-01-21 at 18:36 +, Daniele Varrazzo wrote:
 = alter table commission_rate add constraint check_overlapping
 exclude using gist (payer_id with =, box( point(extract(epoch from
 start_date), extract(epoch from start_date)),
 point(coalesce(extract(epoch from end_date), 'infinity'),
 coalesce(extract(epoch from end_date),'infinity') ) ) with );
 ERROR:  data type integer has no default operator class for access method 
 gist
 HINT:  You must specify an operator class for the index or define a
 default operator class for the data type.

 Can I build a constraint check using both a box (for the range) and an
 integer (for a fkey) or should I go back to the classic trigger + lock
 solution?

 Install btree_gist, and this should work.

 Exclusion constraints are enforced with an index, and an index can only
 have one index access method (btree, gist, etc.). So you need to have
 one index access method that works for both = on integers and  on
 boxes.

Yes, I see the implementation constraint...

 There's no hope of making a btree work for  on boxes, so we need to
 make gist work for = on integers.

Ok, the btree_gist seems exactly the missing link. Will try with that.

Thanks,

-- Daniele

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

Re: [GENERAL] Any way to use refcursors from python?

[Daniele Varrazzo]

On Wed, Dec 29, 2010 at 1:46 PM, Andrew Sullivan a...@crankycanuck.ca wrote:
 Hi all,

 For a little application I'm working on (written in Python), I have a
 number of potentially large result sets that I'd like to return from a
 PL/pgSQL function.  My natural inclination would be to return a
 refcursor.  It seems, however, that psycopg2 can't accept such
 references.

 Have I completely overlooked something (there would be no news in
 that, of course)?  Is there some other interface I ought to be using?

I don't think there is direct support for PL/pgSQL refcursors... not
that I know at least.

There is support for named cursors instead: if you use:

curs = conn.cursor(MYNAME)
curs.execute(SOME SQL)

this will result in a query similar to:

declare MYNAME cursor for SOME SQL

and the curs.fetch*() methods will result in FETCH commands instead of
just client side manipulations. So there may be some small sql you may
execute (may it be select * from my_function()? -- don't know the
syntax to interact with refcursors) to bind the refcursor to a named
cursor.

Failing that, because there is already support for FETCH in named
cursor, it may be easy enough to add refcursors support to the
library.

Feedback is appreciated, either to start designing a refcursors
feature to the library or to mention the named cursors trick in the
docs if it works.

Regards,

-- Daniele

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

Re: [GENERAL] [HACKERS] getting composite types info from libpq

[Daniele Varrazzo]

On Wed, Dec 15, 2010 at 6:56 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Wed, Dec 15, 2010 at 1:25 PM, Daniele Varrazzo
 daniele.varra...@gmail.com wrote:
 Hello,

 when a query returns a composite type, the libpq PQftype() function
 reports the oid of the record type. In psycopg:

     cur.execute(select (1,2))
     cur.description
    (('row', 2249, None, -1, None, None, None),)

    test=# select typname from pg_type where oid = 2249;
     typname
    -
     record

 Is there a way to recursively retrieve the types for the record components?

 not without talking to the server, unless you had previously pulled
 pg_attribute data.

 select * from pg_attribute where attrelid = 2249;

No, there is no such info in pg_attribute: 2249 is the oid for the
type of a generic record, not for a specific type.

 This question is more appropriate for -general, but what are you trying to do?

Added -general in copy: please remove -hackers in your reply if you
think this thread is out of place.

I'm hacking on psycopg. Currently it uses PQftype, PQfname and related
functions to inspect the PQresult received after a query in order to
build the python representation of the record. But the inspection is
flat: if the record contains a composite structure it is currently
returned as an unparsed string:

 cur.execute(select ('1'::int, current_date), current_date)
# the date outside the record is easily parsed, for the one inside
the record
 cur.fetchone()
('(1,2010-12-16)', datetime.date(2010, 12, 16))
 cur.description  # name and oid are the first two fields
(('row', 2249, None, -1, None, None, None),
 ('date', 1082, None, 4, None, None, None))

As the record is created on the fly, I assume there is no structure
left in the catalog for it. If I instead explicitly create the type I
see how to inspect it:

test= create type intdate as (an_int integer, a_date date);
CREATE TYPE

 cur.execute(select (1, current_date)::intdate, current_date)
 cur.fetchone()
('(1,2010-12-16)', datetime.date(2010, 12, 16))
 cur.description
(('row', 650308, None, -1, None, None, None),
 ('date', 1082, None, 4, None, None, None))

test= select attname, atttypid from pg_attribute where attrelid = 650306;
 attname | atttypid
-+--
 an_int  |   23
 a_date  | 1082

but even in this case it seems it would take a second query to inspect
the type and even here It doesn't seem I could use
PQgetvalue/PQgetlength to read the internal components of the
composite values.

The goal would be to have the query above translated into e.g. a
nested tuple in python:

((1, datetime.date(2010, 12, 16), datetime.date(2010, 12, 16))

and I'd like to know:

1. do I get enough info in the PGresult to inspect anonymous composite types?
2. do I get such info for composite types for which I have schema info
in the catalog, without issuing a second query? (which I don't feel it
is a driver's job)
3. is there any libpq facility to split the string returned after a
composite types into its single components, without having to write a
parser to deal with commas and quotes?

 cur.execute(select ('a'::text, 'b,c'::text, 'd''e'::text,
'f\g'::text))
 print cur.fetchone()[0]
(a,b,c,d'e,fg)

4. are by any chance those info passed on the network, maybe available
in an internal libpq structure, but then not accessible from the libpq
interface?

Thank you very much.

-- Daniele

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

[GENERAL] Info about concurrent sequential scans

[Daniele Varrazzo]

Hello,

at Prato PgDay in 2007 I remember hearing in a speech about a (then
yet to come) seqscan piggyback feature, allowing concurrent
sequential scans to use the same disk reads. I've now googled for info
about this feature, but I found nothing conclusive (e.g. [1], [2] -
which I don't know where is linked).

So I'd like to know:

is this feature currently implemented (I'm specifically interested in PG 8.3)?
Is there any prerequisite needed to benefit from it (config setting,
query characteristic, etc.)?
Is there any feedback showing the feature kicking in (e.g. an explain
analyze output, log, etc.)?

Thank you very much.

-- Daniele

[1] http://wordpress.metro.cx/2008/02/24/postgresql-83/
[2] http://www.postgresql.org/about/featuredetail/feature.26

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

Re: [GENERAL] Info about concurrent sequential scans

[Daniele Varrazzo]

On Mon, Feb 22, 2010 at 7:59 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
 Daniele Varrazzo daniele.varra...@gmail.com wrote:

 Hello,

 at Prato PgDay in 2007 I remember hearing in a speech about a (then
 yet to come) seqscan piggyback feature, allowing concurrent
 sequential scans to use the same disk reads. I've now googled for info
 about this feature, but I found nothing conclusive (e.g. [1], [2] -
 which I don't know where is linked).

 I think, you means this:

  Concurrent large sequential scans can now share disk reads (Jeff Davis)

 This is accomplished by starting the new sequential scan in the middle
 of the table (where another sequential scan is already in-progress) and
 wrapping around to the beginning to finish. This can affect the order of
 returned rows in a query that does not specify ORDER BY. The
 synchronize_seqscans configuration parameter can be used to disable this
 if necessary

 Source:
 http://www.postgresql.org/docs/current/interactive/release-8-3.html

Thank you very much (and thanks to Bruce too). This is the feature and
I'm checking it works out of the box on PG 8.3

-- Daniele

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

[GENERAL] Altering columns order

[Daniele Varrazzo]

Hi everybody.

Is there any way to alter the order the columns appear in a table (apart from using 
views)?

I tried changing attnum from pg_attribute... but I could only mess the server up! 
Successive INSERT command crashed it until I restored the previous order... :-\

If the answer was You have to create a new table and dump everything into it: is 
there a simple way to move also the foreign keys referencing to it and eventually 
alter other objects depending onto it?

Thanks a lot

Daniele


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org