[PyGreSQL] [ANNOUNCE] Release of PyGreSQL version 6.0

[Christoph Zwerschke]

Hi all,

PyGreSQL 6.0 has been released now.

This version 6.0 does not include new features, but improves developer 
experience with a modernized and more clearly arranged code base that 
also provides type hints.


The supported versions are Python 3.7 - 3.12 and PostgreSQL 10 - 16.

For older Python and PostgreSQL versions, please use PyGreSQL 5.2.5.

See also the changelog here:

https://pygresql.readthedocs.io/en/latest/contents/changelog.html

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] [ANNOUNCE] Release of PyGreSQL version 6.0b1

[Christoph Zwerschke]

Hi all,

PyGreSQL 6.0b1 is now available for testing.

As discussed, version 6.0 does not include new features, but improves 
developer experience with a modernized and more clearly arrange code 
base that also provides type hints.


The supported versions are Python 3.7-3.12 and PostgreSQL 10-16.

https://pygresql.readthedocs.io/en/latest/contents/changelog.html

Please test and let me know if there are any problems and I can ship a 
final 6.0 release at the end of the week.


-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] Roadmap for PyGreSQL

[Christoph Zwerschke]

Hi all,

the lastest release 5.2.5 of PyGreSQL now supports Python from 2.7 to 
3.11 and PostgreSQL from 9.0 to 15.


It has become a burden to test and created builds for this wide range of 
versions, some of which are not officially supported any more. I feel 
the time has come to de-support the older Python and PostgreSQL 
versions, particularly Python 2. This will slim down and simplify the 
code base and allow us to use new features (such as type hints).


My suggestions is to require Python >= 3.6 and PostgreSQL >= 10,
which is still very conservative.

My plan is to implement this in a new release 6.0, without adding any 
new features or breaking changes.


Version 6.1 can then add some of the backward compatible features that 
were originally planned for 5.3, and Version 7.0 will add features and 
make changes which may not be fully backward compatible.


I have already created a branch 5.2.x for bugfix releases, and will work 
on 6.0 in the main branch.


What do you think about this plan?

-- Christoph



___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] [ANNOUNCE] Release of PyGreSQL version 5.2.5

[Christoph Zwerschke]

Hi all,

PyGreSQL 5.2.5 with some bugfixes and support for Python 3.11 and 
Postgres 15 version has been released.



@D'Arcy: We currently have a problem with www.pygresql.org:
- the http link shows an old version
- the https link does not work (wrong domain name in certificate)

The best solution would be if both of these URLS simply redirect
to https://pygresql.github.io/PyGreSQL/.

-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] pygres nose tests

[Christoph Zwerschke]

On 24.06.2022 04:06, Justin Pryzby wrote:

I finally discovered what it is that "nose" does without arguments.

It's skipping executable files - running "nosetests --exe" runs all the tests.

Is it deliberate to skip the others by default ?


Not sure. Anyway, nose is totally outdated. Use pytest or unittest from 
the standard lib to run the tests, e.g. "python -m unittest discover".


-- Christoph



___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] Patch Release 5.2.3

[Christoph Zwerschke]

Hi all,

I just published a 5.2.3 patch release.

Sources and binaries are available on PyPI and GitHub as always.

PyPI: https://pypi.org/project/PyGreSQL/
GitHub: https://github.com/PyGreSQL/PyGreSQL

The ChangeLog is here:
https://pygresql.readthedocs.io/en/latest/contents/changelog.html

If you find any problems with the release,
or if you have suggestions for improvements,
please let us know by creating an issue here:
https://github.com/PyGreSQL/PyGreSQL/issues

Stay healthy and happy.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] array[t] treated as text for unknown type

[Christoph Zwerschke]

On 14.09.2021 03:03, Justin Pryzby wrote:
> The solution is to use oid::regtype (rather than typname::regtype).
>
>  self._query_pg_type = (
>  "SELECT oid, typname, oid::regtype,"
>
> The docs show that's available since v9.3
> https://www.postgresql.org/docs/9.3/catalog-pg-type.html
>
> But I checked that it exists since 8.4, but wasn't documented until
> 160701f6a935d5b0440751c3cb3f70bb59cf5eb1
>
> BTW, I think the casts like ::regtype should be written as
> ::pg_catalog.regtype, same as psql describe.c and pg_dump.c

Thanks Justin. This is probably the best solution - I have updated the 
GitHub issue. Will add a fix and some unit tests for this soon.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] array[t] treated as text for unknown type

[Christoph Zwerschke]

On 10.09.2021 00:41, Justin Pryzby wrote:

python3 -c "import pg; db=pg.DB('postgres'); q=db.query('SELECT 
array_agg(column_name) AS cols FROM information_schema.columns').getresult(); 
print(q[0][0][0])"

I think it should treat this as array[text] rather than text.

But I'm certain that it shouldn't cause errors in the logs:


Hi Justin, thanks for reporting. I filed an issue for this here: 
https://github.com/PyGreSQL/PyGreSQL/issues/65


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] inserttable and endcopy()

[Christoph Zwerschke]

Thanks for creating the issues.

Was not able to work on PyGres this weekend but it's on my to do list.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] inserttable and endcopy()

[Christoph Zwerschke]

Hi Justin,

thanks for the heads up - can you create separate GitHub issues for 
these things and everything else that should go into the patch release?


I just added another issue today. Will try to create a bugfix release 
covering these things over the weekend then.


Yes, the frontend/backend-protocol doesn't matter for us since libpq 
takes care of that.


-- Christoph


On 11.06.2021 16:43, Justin Pryzby wrote:

It looks like inserttable() fails to call endcopy() when it raises exceptions
after calling "copy from":

 else {
 PyErr_SetString(
 PyExc_TypeError,
 "The second argument must contain a tuple or a list");
 return NULL;
 }

This causes protocol errors:

$ python3.5 -c "import pg; d=pg.DB('dbname=postgres host=/tmp'); 
d.inserttable('loc2', ['b', 'c'])"
Traceback (most recent call last):
   File "", line 1, in 
TypeError: The second argument must contain a tuple or a list
...
2021-03-05 14:36:41.297 CST client backend[7161] [unknown] ERROR:  unexpected 
message type 0x58 during COPY from stdin

It looks like this was previously broken by 7f1e84d88, fixed (incidentally) by
b8bd9f763, and most recently broken by b8bd9f763.

Also, it looks like PQputline is deprecated.

Also, note that postgres v14 has removed protocol version 2 - I think we don't
have to care since we just link to lipq.

Also, is it possible to make a patchlevel release for the last few bugfixes ?

Cheers,
Justin

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] doc fixes

[Christoph Zwerschke]

On 28.03.2021 16:54, Justin Pryzby wrote:

As far as I see, these are already incorporated in the Git repo.


I don't think so
https://github.com/PyGreSQL/PyGreSQL/blob/master/docs/contents/pg/connection.rst


Ah, ok, I had already applied your patch when I looked :)

Pushed to the repo now.

-- Christoph




___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] doc fixes

[Christoph Zwerschke]

On 28.03.2021 16:11, Justin Pryzby wrote:
> Here's some doc fixes.  A couple of these I've had floating around in
> an SVN> for a few years now...  Feel free to sit on this patch and
> I'll include it again with my next patch series, whatever that is.

As far as I see, these are already incorporated in the Git repo.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] asynchronous command processing

[Christoph Zwerschke]

On 27.03.2021 22:45, Justin Pryzby wrote:

It looks like the PyArg_ParseTuple() can/should just be removed from the NOARGS
case, including poll().


That's true. I have fixed this in the master branch now.

The async support still needs some unit testing, and it seems some 
important methods are also not yet implemented.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] PyGreSQL 5.2.2 released

[Christoph Zwerschke]

Hi all,

I just published a 5.2.2 bugfix release.

Sources and binaries are available on PyPI and GitHub as always.

Homepage: https://pygresql.org/
PyPI: https://pypi.org/project/PyGreSQL/
GitHub: https://github.com/PyGreSQL/PyGreSQL

The ChangeLog is here:
https://pygresql.org/contents/changelog.html

If you find any problems with the release,
or if you have suggestions for improvements,
please let us know by creating an issue here:
https://github.com/PyGreSQL/PyGreSQL/issues

Stay healthy and happy.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] PyGreSQL 5.2.1 available

[Christoph Zwerschke]

Hi all,

I just pushed out a 5.2.1 release that supports both PostgreSQL 13 
(released today) and Python 3.9 (released next month).


Sources and binaries are available on PyPI and GitHub as always.

Homepage: https://pygresql.org/
PyPI: https://pypi.org/project/PyGreSQL/
GitHub: https://github.com/PyGreSQL/PyGreSQL

If you find any problems with the release,
or if you have suggestions for improvements,
please let us know by creating an issue here:
https://github.com/PyGreSQL/PyGreSQL/issues

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] ANN: PyGreSQL 5.2 released

[Christoph Zwerschke]

PyGreSQL 5.2 has been released today

Download from PyPI:
https://pypi.org/project/PyGreSQL/5.2/#files

More downloads in other formats:
https://github.com/PyGreSQL/PyGreSQL/releases/tag/5.2

Changes:
https://pygresql.org/contents/changelog.html

Thanks to all who sent in contributions and suggestions on the mailing 
list and helped with this release.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Timeframe for 5.2 or 5.1.3 release

[Christoph Zwerschke]

Am 20.06.2020 um 15:58 schrieb Justin Pryzby:

I think you could have just as well said the opposite (passing the
column names in the unesaped table names was a bad idea), since I was
on the verge of suggesting we just do document that :)

Right, you can argue that the feature existed all the time already :)

Will fix the typo and then have a look at #29.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Timeframe for 5.2 or 5.1.3 release

[Christoph Zwerschke]

Am 20.06.2020 um 04:02 schrieb Justin Pryzby:

I'm attaching my WIP patch.  It will needs to be cleaned up, since (1) and (2)
will surely cause rebase conflicts, so this is just a POC.


Thank you. I have commited this after making a few amendments:

https://github.com/PyGreSQL/PyGreSQL/commit/f82d4cd20dfa66ec791091581e60e299b329c70e

This works with Python 2 and 3 now (we will drop Python 2 support 
probably in version 6, but for now we must stay compatible).


I have also added some more tests. Escaping the table and column names 
was a good idea (you must free the space for the escaped name though).


Does my commit cover everything you mentioned, can we close the issue?

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Timeframe for 5.2 or 5.1.3 release

[Christoph Zwerschke]

Am 20.06.2020 um 00:54 schrieb Justin Pryzby:

I think these would be easy to add.

https://github.com/PyGreSQL/PyGreSQL/issues/29
The pgquery type should have a method listtypes() #29
I sent a patch for this to the list last year

https://github.com/PyGreSQL/PyGreSQL/issues/24
Allow inserttable() to take columns as parameter #24
This is simple enough I can probably put something together


Ok, I will look into these two issues tomorrow.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Timeframe for 5.2 or 5.1.3 release

[Christoph Zwerschke]

My plan is now to release version 5.2 tomorrow. Are there some things 
that I should still consider for the release? Then please let me know.


As a new feature, I have included support for asynchronous processing, 
which was suggested on this mailing list quite a while ago. The feature 
is still experimental and only rudimentarily tested, but I think we can 
stabilize this in patch releases instead of creating beta releases, 
since the rest of the package should be stable.


-- Christoph



Am 15.06.2020 um 19:51 schrieb Tyler Ramer:

Hi,

We recently submitted a patch, 
https://github.com/PyGreSQL/PyGreSQL/commit/b1e040e989b5b1b75f42c1103562bfe8f09f93c3*, 
* to the pygresql source.


We were wondering what the timeframe might be to getting this fix in a 
versioned release available on PyPi, as part of 5.2 or 5.1.3 or so.


Thanks much!

Tyler Ramer  |  Software Engineer, Tanzu Data |tra...@vmware.com 



___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plan to release 5.1.2

[Christoph Zwerschke]

Am 19.06.2020 um 23:20 schrieb Justin Pryzby:
>>> Also, I suggest to make sure the "statistics" here links to github:
>>> https://pypi.org/project/PyGreSQL/
>>
>> Ok, I have reported the new URL now. May take a while to update.
>
> Looks like this didn't work ?
You mean the repository link is missing?

The reason may be that we don't have any links to the repository in the 
setup script. Added some project URLs now, hope that this helps:


https://github.com/PyGreSQL/PyGreSQL/commit/17f28cdbe7f53459919b1ca8143b14f06d1db7b1

These URLs will be pushed to PyPI with the upcoming 5.2 release.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Timeframe for 5.2 or 5.1.3 release

[Christoph Zwerschke]

I plan to work on PyGreSQL this week and will probably also cut a new 
release then. If there are any other fixes you want to get into the 
release, please open issues on GitHub.


-- Christoph


Am 15.06.2020 um 19:51 schrieb Tyler Ramer:

Hi,

We recently submitted a patch, 
https://github.com/PyGreSQL/PyGreSQL/commit/b1e040e989b5b1b75f42c1103562bfe8f09f93c3*, 
* to the pygresql source.


We were wondering what the timeframe might be to getting this fix in a 
versioned release available on PyPi, as part of 5.2 or 5.1.3 or so.


Thanks much!

Tyler Ramer  |  Software Engineer, Tanzu Data |tra...@vmware.com 



___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql


___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Certificate for www.pygresql.org

[Christoph Zwerschke]

Ok, I think I was able to solve this by removing the "www" from the 
cname file and the setting in the repository. Now "pygresql.org" is 
shown properly via https, and www.pygresql.org redirects to it.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Certificate for www.pygresql.org

[Christoph Zwerschke]

Am 09.06.2020 um 15:03 schrieb D'Arcy Cain:

My FF on FreeBSD doesn't do that.  Doesn't do it on Linux either.
Must be a Windows thing.  Another reason not to run Windows as if I
needed more.


Don't think FF would behave differently on Windows in such matters. I 
think the issue is that it happens when you visited the https address 
once - then FF will try it the next time as well.


>> When I click on the certificate, I see one from "Let's Encrypt", but
>> only valid three months from 02/21 to 05/21/2020.
>
> No idea who got that.  We sell certs and that is not where we get 
them from.


Ok, I found that it's actually GitHub (pages) who does this. They 
partner with Let's Encrypt to add certificates automatically. It seems 
something went wrong in that process. GitHub shows a "Certificate 
Request Error: Certificate provisioning will retry automatically in a 
short period, please be patient." Obviously the automatic retry doesn't 
work, but I see no option to retry manually.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Certificate for www.pygresql.org

[Christoph Zwerschke]

Am 09.06.2020 um 13:25 schrieb D'Arcy Cain:
> On 2020-06-09 07:19, Christoph Zwerschke wrote:
>> Looks like the certificate for www.pygresql.org has expired.
>> D'Arcy, since you own the domain, can you renew it?>
> I don't think that we ever had a certificate for PyGreSQL.org.  Where
> do you see the expired one?  Do we need a certificate?

When you enter "pygresql.org" in the address bar of firefox, you will be 
sent to the https address nowadays. Also, modern browsers mark http as 
"not secure" with a warning sign which looks ugly.


When I click on the certificate, I see one from "Let's Encrypt", but 
only valid three months from 02/21 to 05/21/2020.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] Certificate for www.pygresql.org

[Christoph Zwerschke]

Looks like the certificate for www.pygresql.org has expired.
D'Arcy, since you own the domain, can you renew it?

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] PyGreSQL as dialect for SQLAlchemy

[Christoph Zwerschke]

SQLAlchemy is currently deprecating many of the built-in dialects, one 
of them for PyGreSQL, and are asking us to provide it as external 
dialect in the future.


I will create a separate repository for that under 
https://github.com/PyGreSQL - if anybody is interested in helping with 
this let me know.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] PyGreSQL 5.1.2 has been released

[Christoph Zwerschke]

The PyGreSQL 5.1.2 bugfix release is available for download now.

I have created a new maintenance branch 5.1.x, the master branch will be 
used for 5.2 now.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plan to release 5.1.2

[Christoph Zwerschke]

Am 19.04.2020 um 03:23 schrieb Justin Pryzby:
> Also, I suggest to make sure the "statistics" here links to github:
> https://pypi.org/project/PyGreSQL/

Ok, I have reported the new URL now. May take a while to update.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] pylint and c module attrs

[Christoph Zwerschke]

Am 18.04.2020 um 20:18 schrieb Justin Pryzby:

It works fine when I use import _pg, which is what I started doing at
telsasoft before raising this issue, and what I'll plan to continue.
The explanation is probably the same, because pylint doesn't analyze C 
extensions by default, so it ignores any imports from there. That's why 
your import statement is ignored and why it does not know that pg has 
these attributes because they were imported from the C extension.


> The pep8 style portion of flake8 is so naggy as to be useless to me.

Note that you can ignore and modify various checks that you find too 
restrictive in pylint and flake8 using their configuation files, 
adapting them a bit more to your personaly/company style.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] pylint and c module attrs

[Christoph Zwerschke]

Am 18.04.2020 um 17:23 schrieb Justin Pryzby:
> That didn't work.
>
> It did work to do this:
> ProgrammingError = _pg.ProgrammingError
>
> Maybe __all__ can *limits* what's visible but not extend it.  ?

It actually only defines what will be imported when you do a wildcard 
import, and I suspected it could be used by pylint as well.


I have added __all__ attributes to pg and pgdb now anyway.

If pylint still complains, then it is a pylint issue. The message "No 
name ProgrammingError in module pg" is definitely wrong - it looks like 
pylint cannnot interpret the "from _pg import *" statement, maybe 
because it cannot process C extensions.


Others have reported this issue already here:

https://github.com/PyCQA/pylint/issues/1138
https://github.com/PyCQA/pylint/issues/1524

Probably best to use the workaound given there,
i.e. setting extension-pkg-whitelist=pg in this case.

You can also try using flake8 instead of pylint, they have both their 
pro's and con's.


-- Christoph


___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plan to release 5.1.2

[Christoph Zwerschke]

Am 18.04.2020 um 15:51 schrieb Justin Pryzby:

I guess it's a bugfix release.  These is outstanding and small:


Hi Justin, yes, quick bugfixes only. Larger changes will go to 5.2.

In 5.2 I also want to desupport older Python versions like 2.6, 3.3 and 
3.4. And start supporting static linking, and release Windows binaries 
with pqlib statically linked.


In Version 6 we will then support Python 3 only (probably 3.6+).

I have added your small change now, is this what you meant?

https://github.com/PyGreSQL/PyGreSQL/commit/8398f2fba59662d2d4ecbbc4fbcca952e14f6763

-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] Plan to release 5.1.2

[Christoph Zwerschke]

I plan to publish version 5.1.2 tomorrow, which fixes build problems 
with MacOS and improves the handling of build options. If there are any 
other small fixes that need to be included, let me know.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 28.03.2020 um 18:56 schrieb Justin Pryzby:
> Maybe that should be --with-ssl-info ?  I found this:

Not sure about that. In the context of setup.py and negative_opt, I only 
see the 'no-' pattern being followed. Also, this is backward compatible.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 28.03.2020 um 16:21 schrieb Justin Pryzby:

Also, you have every option defaulting to try, with the only consequence of an
unsupported PG version being a warning.  I mentioned that packages (at least in
the past for Debian) were encouraged to compile with --enable-foo --enable-bar..
..such that a packages that was *expected* to compile again libfoo would fail
loudly at the compile stage if it wasn't there or was somehow broken.


Thanks for the quick feedback, Justin.

I have addressed both of your points in 
https://github.com/PyGreSQL/PyGreSQL/commit/03858234c9077ea7e492a4bbd0664b9fad74b70d


Better?

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Ok, I found that distutils even has a way to define "negative" options 
and used it in setup.py now:


Please have a look at: 
https://github.com/PyGreSQL/PyGreSQL/commit/4b88231afd6bdfb40dca20780cdf006fe2346ea8


Let me know what you think about this change.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 25.03.2020 um 12:28 schrieb Justin Pryzby:

I think (1) is more common, I think that's what autoconf and gnu
tools use.
In that case, maybe we should use enable/disable or with/without as 
prefixes, like autotools? But I think it's not very common as setup 
option. Also, by using the old option name and just adding the "no-" 
variant, we would stay backward compatible.


-- Christoph


___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 25.03.2020 um 10:34 schrieb Justin Pryzby:

I think I would leave the "positive" variable names, and *add* negative forms
of the commandline options.  --no-foo and/or --foo=no


Yes, that would be nice. Unfortunately, the options are not interpreted 
by us, but by distutils. The way the options are currently defined in 
PyGreSQL they act as booleans that do not take arguments, i.e. you can 
set --foo, but not --foo=false. Unfortunately how the options are 
interpreted is not well documented (at least I find nothing), so I had 
to look into the Python source and found distutils uses getopt to parse 
the raw options. Therefore it is possible to require arguments by 
writing a "=" after the name of the argument in user_options, e.g.


('escaping-funcs=', None, "enable string escaping functions"),

When defined that way, we will be able to pass "--escaping-funcs=no" or 
"--escaping-funcs false" as an option. When 'escaping-funcs' is added to 
boolean_options, then the argument needs to be something like 0/false/no 
or 1/true/yes and will be converted to a Python bool.


Unfortunately, getopt does not allow default arguments. So it looks like 
we have to choose between:


1) Either two options --escaping-funcs and --no-escaping-funcs, both 
without arguments or

2) one option --escaping funcs with a mandatory boolean argument

In both cases, when we set self.escaping_funcs = None in 
initialize_options, we can implement the ternary on/off/default.


Not sure whether 1) or 2) is better, what do you think?

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 25.03.2020 um 02:20 schrieb Justin Pryzby:
> The old stuff can be removed eventually.  I like this:
>
>  if self.escaping_funcs and pg_version >= (9, 0):
>  define_macros.append(('ESCAPING_FUNCS', None))
> That allows a packager to disable a feature
> if they want the abilityto run> against old libpq.

The problem is that these switches are by default set to true. The idea 
was that you set them to false on the command line if you don't want 
these features to be compiled. However, I think that never worked since 
you simply can't set these flags to false on the command line, you only 
can set them to true by specifying the names of the options (i.e. pass 
--escaping-funcs as option).


So we need to change that and instead use the negatives of these flags 
everywhere as options i.e. "no_escaping_funcs" in this case. I usually 
avoid negative forms since "Can I not have my coffee with no sugar" 
sounds silly when I can say "Can I have coffee with sugar". But in the 
case where "sugar" is always the default, it's ok, since you only need 
to say "no sugar please" and never need to ask for sugar explicitly. (I 
hope this was not confusing :)


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 24.03.2020 um 05:46 schrieb Justin Pryzby:

This patch got stuck due to our limited understand of python's
dynamic loading of shared libraries.  I picked it up again ince I
have some downtime..
Thanks for working on this problem. Would be great if we could resolve 
it. Otherwise it always hinders us supporting the latest features.


> Since it's BIND_NOW, it means that any python program linked to
> pygres willsimply fail at startup in the unlikely even that it was
> compiled against libpq for PG12 but runs against an lib older

It's unlikely when you compile yourself, but Windows users normally 
don't do that since they have no compiler. So if we ship a version that 
was compiler against Pg12, it will not run for users with Pg11 installed 
on their machine. On the other hand, Windows DLL work a little bit 
differently anyway, maybe we can find a different solution there.


Maybe where we release binaries (currently only for Windows) we could 
ship them with a matching libql dll that will be installed with Pip 
alongside the PyGres dll? Or maybe link the pqlib statically into the 
PyGres dll in that case? This would make it even more convenient for 
Windows users since they don't need to install Postgres.


-- Christoph








___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 12.11.2019 um 14:09 schrieb D'Arcy Cain:
> Is the code ready now for a release?  Does anyone know of anything
> missing that should be in it?

That code is ready to be released as 5.1.1.

We had one big open question that still needs to be resolved: How to 
deal with new/optional functionality, e.g. the memsize() function new in 
PG 12. We actually wanted to add this into 5.1.1 as well, but it seems 
we should postpone it.


The problem here is that if we build the shared lib with these 
functions, they don't run on systems with older pqlib. This is 
particulary a problem when you distribute PyGreSQL as a binary (the 
usual way on Windows). Therefore we would need to ship binary packages 
not only for various Python versions, but also different Postgres 
versions (the whole matrix of combinations), which is awful.


Two related problems:
- The current way how build switches were implemented in setup.py is not 
really useful, because they can never be set to false. This needs to be 
changed. See https://github.com/Cito/PyGreSQL/issues/12
- Lazy binding would theoretically allow using functions of newer 
pqlibs, but somehow did not seem to work as expected in practice, this 
needs to be further investigated.


I suggest moving the solution of these problems to 5.2 since the new 
implementation of build switches can be considered a breaking change.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 06.10.2019 um 21:39 schrieb Justin Pryzby:
> That doesn't explain it :(
>
> Find attached t.c building t.so...

Thanks. At least we have ruled that out :/

Winter is coming and maybe we can find some rainy weekend to investigate 
deeper and learn a thing or two about the sacred science of building 
shared libraries...

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 06.10.2019 um 15:20 schrieb Justin Pryzby:
> Remember, lazy binding is the default on my machine, and my patch
> doesn't DWIW here..  So I don't think adding lazy link options
> is going to help.  I think because of something python is doing (??)

Another difference is that in your example you're using libpq as shared 
lib directly, whereas with Python you create a shared lib that then uses 
libpq as a shared lib. Maybe it has something to do with that?


I kept the GitHub issue open as a reminder, and also created a Trac 
ticket referencing it.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 06.10.2019 um 01:15 schrieb Justin Pryzby:

On Sat, Oct 05, 2019 at 11:43:23PM +0200, Christoph Zwerschke wrote:
..but I don't think it's a solution for pygres ?  I tried it and still get a
symbol resolution error.  So, unless someone knows or finds better, it means
there's nothing stopping installing of pygres compiled against one library with
an earlier library, even though that combination might be broken.

My #ifs and ifs don't help, and I suspect your setup.py stuff doesn't either (?)


When I tried building with setup.py, I noticed that Python links the 
shared lib in a separate step after the compilation. Options for this 
second step must be set in extra_link_args, not in extra_compile_args.
I also noticed in the output that the shared lib is linked with the 
options -Wl,-Bsymbolic-functions and -Wl,-z,relro. Maybe this is what 
defeats the lazy binding?


Unfortunately. adding the options for lazy loading to extra_link_args, 
CFLAGS or LDFLAGS did not help, maybe because they are only appended 
(added) to the options above, and only the first option is effective?


Running out of time, need to investigate when I find some time again.

Or maybe someone with more knowledge of these things can explain what's 
going on here and how we can avoid the "undefined symbol" errors when 
using an older libpq version.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 05.10.2019 um 23:33 schrieb Justin Pryzby:
> You probably realized that my email failed to show that I
> downgraded libpq between invocacations of "PGDATABASE=postgres ./t 1"

Yes, I understood what you were doing. But I could only reproduce it 
when adding the "-Wl,-z,lazy" option. Not sure why it's not the default 
on my machine.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 05.10.2019 um 23:10 schrieb Christoph Zwerschke:
Just tried what you did with that t.c program, but strangely I get the 
"undefined symbol" error when I downgrade libpq and run "./t 0", even 
when LD_BIND_NOW is not defined. I am using Ubuntu instead of Debian, 
but that shouldn't matter.


Ok, found the problem - I needed to explicitly add the "-Wl,-z,lazy" 
option when compiling t.c to make it use lazy binding. For some strange 
reason, it does not seem to be the default.


Maybe we need to add this to the extra_compile_args in the setup.py file 
as well?


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 05.10.2019 um 18:47 schrieb Justin Pryzby:
> On Sat, Oct 05, 2019 at 06:37:36PM +0200, Christoph Zwerschke wrote:
>> Are you sure you compiled that with the pgconfig.h of Postgres 12?
>> setup.py is using the includes specified by the pgconfig tool.
> The intended behavior is to hit the runtime version check and then
> return None.
> To check, I compiled against pg12 and then downgraded libpq to v10.
> Built like: PATH=/usr/lib/postgresql/12/bin:$PATH ./setup.py build
> If I hadn't, then it wouldn't have referenced PQresultMemorySize at
> all, right ?

Ah, yes, you're right. The runtime checks are useless because you can't 
even load a shared library that doesn't support all the symbols that you 
are referencing somewhere.


That's actually the reason why I proposed supporting a "compatible 
version" option when building PyGreSQL, so that it's possible to create 
PyGreSQL binaries that can be used with older libpq versions.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 05.10.2019 um 18:04 schrieb Justin Pryzby:

On Sat, Oct 05, 2019 at 04:48:40PM +0200, Christoph Zwerschke wrote:

Find attached minimal patch.


Thank you, looks fine. I've already added docs and tests as well, but not
yet committed to trunk.


I didn't mean you had to stop and do that - I hammered it out while waiting for
pg_checksums to run :)


And I just had some spare time and was working on PyGres anyway :)


Good idea.  But I wonder whether it should be a property, which you suggested
before for types of queryResult columns (for which I sent a WIP patches July 19
and 20).


I thought about that. But this is a module level function and there are 
no module level properties in Python. Maybe we could use PEP 549 or 
PEP562, but these require Python 3.7. We could provide it as an 
attribute but that means we need to initialize it by making a call once 
when importing.



|$ PYTHONPATH=build/lib.linux-x86_64-2.7/ python2.7 -c "import pg; print 
pg.DB('postgres').query('SELECT generate_series(1,)').memsize()"
|Traceback (most recent call last):
|  File "", line 1, in 
|  File "pg.py", line 27, in 
|from _pg import *
|ImportError: /home/pryzbyj/src/pygres/build/lib.linux-x86_64-2.7/_pg.so: 
undefined symbol: PQresultMemorySize


Are you sure you compiled that with the pgconfig.h of Postgres 12? 
setup.py is using the includes specified by the pgconfig tool.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [PATCH] PQresultMemorySize

[Christoph Zwerschke]

Am 05.10.2019 um 02:07 schrieb Justin Pryzby:

On Wed, May 15, 2019 at 08:44:49AM -0500, Justin Pryzby wrote:

Also, as of PG12 (unreleased), there'll be this new interface:
https://www.postgresql.org/docs/devel/release-12.html#id-1.3.7
|Add libpq function to report the memory size of the query result (Lars Kanis, 
Tom Lane)
|The function is PQresultMemorySize().

I'd propose to include that with the first release following PG12...although I
think you'd have to check PG_VERSION_NUM or similar, which I see isn't
currently being done.


Find attached minimal patch.


Thank you, looks fine. I've already added docs and tests as well, but 
not yet committed to trunk.


In addition to query.memsize() I've also added get_pqlib_version() as a 
module level function.


But I wonder if we should create a new minor version (5.2) instead of 
patch release (5.1.1) since it's new functionality?


Another thing I'd also like to address is the overall handling of 
functionality that is optional or does only work with newer pqlib 
versions. Currently these are behind compiler switches which are set in 
setup.py, by default they are enabled if the pqlib version is new enough 
to support the respective functionality.


Alternatively, the pqlib version could be checked directly using 
pg_config.h in the code instead of using the various switches,
like you are doing here. I'd like to handle this more consistently 
through the code. But this would also warrant a new 5.2 version:

see https://github.com/Cito/PyGreSQL/issues/12

-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 03.10.2019 um 14:25 schrieb D'Arcy Cain:
> On 2019-10-03 8:14 a.m., Christoph Zwerschke wrote:
> I suspect that a month should be plenty of time to test everything so 
> I am looking at a mid-November release.  Does that work for everyone?


I don't think we need to wait until Py 3.8 is published - the rc is 
already available and close enough for testing. I fixed the deprecation 
issue and all tests are green now with Py 3.8.


Tried with PostgreSQL 12 today as well. This needs some attention 
because the "with oids" clause has been removed, which is used in some 
tests. Will check and make amendments today, but I assume this is not a 
big issue and PostgreSQL 12 will run fine otherwise.


Do you think it would be possible to get the release out this weekend 
before you move?


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 02.10.2019 um 22:53 schrieb Jacob Champion:

On Wed, Oct 2, 2019 at 12:52 PM Justin Pryzby  wrote:

It looks good to me.


Looks correct to my eye as well.


Thanks for the feedback. I have now also added a regression test that 
can also serve as a demonstration of the problem.


Before releasing PyGres we should make sure that everything works 
properly with Py 3.8 as well which will be released in 11 days,

and PostgreSQL 12 which should be released today.

Just did some testing with Py 3.8, everything looks good except there is 
a deprecation warning that needs attention 
(https://bugs.python.org/issue36381). Will try to fix it this week as well.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 24.09.2019 um 01:57 schrieb Shoaib Lari:
I  think it is definitely preferable that PyGreSQL should add schema 
qualifications to all operators and system tables.

Please let me know if there are any plans for doing so.


I have now implemented this in r1019.
http://trac.vex.net:8000/pgtracker/changeset/1018

If this looks good to everyone and I haven't forgotten anything, we can 
cut a security release 5.1.1.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 24.09.2019 um 01:57 schrieb Shoaib Lari:
I  think it is definitely preferable that PyGreSQL should add schema 
qualifications to all operators and system tables.


Please let me know if there are any plans for doing so.


Thanks Shoaib and Jacob for the reminder. Since there was no negative 
feedback and there are valid reasons to make this change, I think we 
should do it - will try to work on it this week.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Plans for Addressing CVE-2018-1058 in PyGreSQL

[Christoph Zwerschke]

Am 16.08.2019 um 19:58 schrieb Shoaib Lari:
> I was wondering if the PyGreSQL community has any plans to address
> PostgreSQL security vulnerability
> 
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path.


Hi Shoaib, thanks for bringing this to our attention.

> Now connect to the postgres database thorough pygresql, and issue a
> simple query.
>
> import pgdb
> conn = pgdb.connect(database='postgres')
> c = conn.cursor()
> c.execute('SELECT 1')
>
> You will get the following output:
>
> WARNING:  trojan

So what's happening here is that the equality operator used in the 
internal query used by the type cache is called which triggers the 
warning above because you have overridden the operator in the public 
schema which takes precedence before the default one in pg_catalog.


Now, if someone was able to do that I think your goose has been cooked 
anyway since nearly every query will be affected.


The internal query used by the type cache looks something like this:

SELECT oid, typname, ... FROM pg_type WHERE oid=%s

As far as I understand, to make things secure at least what concerns 
PyGreSQL, we would need to change that query to:


SELECT oid, typname, ... FROM pg_catalog.pg_type
WHERE oid OPERATOR(pg.catalog.=) %s

And there's another query for composite types that needs to be fixed in 
a similar way. We would also need to fix the internal query in the 
classic pg.py module, which has not only a type cache but also other 
higher level functions which run internal queries.


This would make the queries look more ugly and lengthy. Not sure about 
the performance impact - maybe it would be even faster because of the 
explicit schema name.


What do others think? Should we make these changes?

The guide that you linked to says this:

"If you write your queries with specific schema.object form, including 
objects that exist in the pg_catalog (e.g. calling SELECT 
pg_catalog.lower('ALICE');), then you are not immediately vulnerable to 
this issue."


But it does not mention operators in this context which are easily 
overlooked and which will make any query secured that way immediately 
become very ugly. In practice I think that's not feasible except when 
all queries are created automatically e.g. by an ORM.


I think in practice you need to rely on users not being able to create 
objects in the public schema, otherwise you're set for deeper troubles 
anyway, no matter how secure your API is.


-- Christoph














___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] pylint and c module attrs

[Christoph Zwerschke]

Am 26.07.2019 um 05:20 schrieb Justin Pryzby:

> Is there any way to avoid output like this?
>
> * Module telsasoft.db
> E: 15, 0: No name 'ProgrammingError' in module 'pg'
> (no-name-in-module)
> ...
>
> I assume it's related to this:
>
> |def __getattr__(self, name):
> |# All undefined members are same as in underlying connection:
> |if self.db:
> |return getattr(self.db, name)
> |else:
> |raise _int_error('Connection is not valid')

The errors complain about not existing members of the module, but the 
code above gets members of the connection, not of the module.


Also, these look like linter errors to me, not runtime errors.

Can you check whether the telasoft module is linted somewhere?

It is probably caused by pg importing the objects from the C extension 
with "from _pg import *", so the linter does not know that these objects 
exist. You can try whether an explicit import of these symbols makes the 
error message go away. Maybe we can make the "import *" explicit then.


-- Christoph



___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] largeobject out of scope deallocated outside of txn

[Christoph Zwerschke]

Thanks for reporting. Created a ticket and will try look into this over 
the weekend.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] PyGreSQL #82: The pgquery type should have a method listtypes()

[Christoph Zwerschke]

Am 20.07.2019 um 02:44 schrieb Justin Pryzby:
>> You would get the types via attnames().
> I'm not sure if I understood the last sentence.
> Do you mean the types of column in a table ?
> That's not the same as types of result columns, though.

Sure, I just wanted to explain why Pygres did not have such a function - 
the classic API was focused mainly on working with individual tables.


I have added your suggestion to ticket #82 with milestone set as 5.2.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] typos

[Christoph Zwerschke]

Am 15.06.2019 um 03:08 schrieb Justin Pryzby:

Thanks.  What does it take to update the web docs ?


https://pygresql.readthedocs.io/
-> I need to push the current trunk to GitHub (done)

http://www.pygresql.org/
-> D'Arcy needs to run the mkdocs script on the server

-- Christoph






___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] should query.source() be documented?

[Christoph Zwerschke]

Am 09.06.2019 um 00:38 schrieb Justin Pryzby:
> I see this interface has been around since PyGreSQL 3.1 (c. 2000),
> but "source objects" are not documented.
>
> I'm referring to these:
> |pg.DB('postgres').source()
> |pg.connect('postgres').source()

This is deliberate. It's an internal object that should be considered an 
implementation detail of the DB-API 2 interface. We don't want to make 
it an official API.


> Also, I noticed this behaves strangely:
 pg.DB('postgres').source()
> Traceback (most recent call last):
>  File "", line 1, in 
> pg.OperationalError: Connection has been closed

This is probably because the db object immediately gets out of scope and 
its connection gets closed, so the source object cannot work.


You need to keep the db object alive, e.g. like this:

with pg.DB('postgres') as db:

source = db.source()
source.execute("select 'source'")
print(source.fetch(1))
source.close()

query = db.query("select 'query'")
print(query.getresult())

But again, use the source() at your own risk, Luke!

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] column types and libpq info functions

[Christoph Zwerschke]

Am 07.06.2019 um 20:03 schrieb Justin Pryzby:

|Add libpq function to report the memory size of the query result (Lars Kanis, 
Tom Lane)
|The function is PQresultMemorySize().


We already have a ticket for this:
http://trac.vex.net:8000/pgtracker/ticket/80


pgdb has
|Cursor.coltypes
|The list of columns types of the current result set
|The values in this list are the same values as the type_code elements in the 
Cursor.description attribute. Always use the latter if you want to remain 
standard compliant.

But I was surprised to find that pg doesn't expose anything similar.  I
wondered whether we shouldn't add interfaces for PQftype and maybe PQfmod,
pehaps by exposing query_obj->col_types.


Yes, that's really lacking. The interface has grown over time and I 
think originally, the focus was on the entity methods like get() and 
insert() and update(). You would get the types via attnames().


Created a ticket for this now:
http://trac.vex.net:8000/pgtracker/ticket/82

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] typos

[Christoph Zwerschke]

Am 07.06.2019 um 19:03 schrieb Justin Pryzby:
> Here's another:
> namedresult/namediter – get query values a named tuples
>
> should be AS named tuples ?

This is all fixed in the trunk now, thank you.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements and adaptation

[Christoph Zwerschke]

Am 04.06.2019 um 03:09 schrieb Justin Pryzby:
> On Wed, May 22, 2019 at 11:01:12PM +0200, Christoph Zwerschke wrote:
>> Not sure what we should do about it? Just better documentation?
>
> I guess the opened question in my mind is exactly which types need/not
> be adapted?  For our purposes, I can call
> "v if type(v)!=list else adapt(v)",
> but what's the general incantation ??

Non-simple types like arrays, records, json, bytea need adaptation. 
adapt() figures it out automatically, in case of doubt you can simply 
pass it everything. And if there is ambiguity and the automatic 
adaptation does not work, you can pass the type as second parameter.


I have created http://trac.pygresql.org:8000/pgtracker/ticket/81 as a 
reminder that this needs better documentation/support.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements and adaptation

[Christoph Zwerschke]

Am 22.05.2019 um 21:00 schrieb Justin Pryzby:
> It looks like this doesn't work correctly.
> query_prepared sends it sargumens to postgres without adaptation.

Only the query_formatted() method adapts the parameters, that's right. 
query() and query_prepared() pass them as strings.


You need to adapt the parameters manually with db.adapter.adapt().

Not sure what we should do about it? Just better documentation? 
Automatically adapt in query() and query_prepared()? But it would good 
to have the "raw" queries as well for performance and cases where the 
adaptation does not work. Also, it should be possible to pass types for 
adaptation like in query_formatted - otherwise the types are guessed 
from the Python types, this might be slower and not always work properly 
when there is ambiguity in rare cases with special types.


-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] 5.0.7 and 5.1 are on PyPI now

[Christoph Zwerschke]

Uploaded everything to PyPI now:
https://pypi.org/project/PyGreSQL/#files

The Windows binaries are here as well:
https://www.vex.net/~cito/distrib/

@D'Arcy - you still need to copy the Windows binaries
from there to http://pygresql.org/files/

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

Am 19.05.2019 um 19:23 schrieb Justin Pryzby:
> I think `` should be double-quoted to avoid blowing up if multiple
> "words" are output (I realize that shouldn't happen in this case):

Right, that's a bit more robust and works under Linux and BSD - the 
backticks are executed inside double quotes as well.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

Am 19.05.2019 um 19:23 schrieb Justin Pryzby:
> I think you handle d and f together as:
>
> |chmod +Xr

The plus sign only adds permissions. We also want to remove permissions 
- like when everything is set to 777. This comes close:


chmod -R u=rwX,g=rX,o=rX *

But it's not exactly the same since it does not remove the executable 
bit for files, so using find -type is better.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

Maybe this will do? Should work on both BSD and Linux.

-- Christoph


# Set proper file permissions
find . -type d -exec chmod 755 {} \;
find . -type f -exec chmod 644 {} \;
chmod 755 docs/Makefile docs/make.bat
chmod 755 mktar mkdocs setup.py
# Make sure file permissions have been set
if [ -z `find . -name pg.py -perm 644` ] || \
   [ -z `find . -name mktar -perm 755` ]
then
echo "File permissions could not be set properly."
exit 1
fi

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

Am 19.05.2019 um 17:39 schrieb D'Arcy Cain:
> The stat(1) command itself fails.  There is no --printf option.

Looks like this works only under Linux. BSD has "stat -f" instead. Let 
me see if I can come up with something OS independent.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

There is also another check in mktar that verifies the number of top 
level files. This has increased in trunk because pgmodule.c has been 
split into several files. But you changed that number already.


Maybe in addition to checking the permissions, we could add the 
following lines that just set them properly, before that check:


find -type d -exec chmod 755 {} \;
find -type f -exec chmod 644 {} \;
chmod 755 docs/Makefile docs/make.bat
chmod 755 mktar mkdocs setup.py

But the check should still be there in order to make sure the 
permissions were in fact set properly and we're not running on some 
strange file system that doesn't support unix permissions.


-- Christoph


Am 19.05.2019 um 14:30 schrieb D'Arcy Cain:

There are a few lines in mktar that don't work.  I am not sure that they
ever did.  I want to make the following change just so that I don't keep
getting the usage message.  The script completes anyway so it doesn't
seem critical but if anyone has a better way to do this test I will do
that instead.


___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Tar file creation error

[Christoph Zwerschke]

These lines check that the permissions are set correctly before building 
the source distribution, using a control sample of files. They were 
added to prevent building a source tarball with bad permissions, which I 
think happened once when I was using a vboxsf filesystem and everything 
had 777 permissions. It would be better to keep that check in.


Can you output the $PERMS before the exit to see why it's not working 
for you?


-- Christoph


Am 19.05.2019 um 14:30 schrieb D'Arcy Cain:

There are a few lines in mktar that don't work.  I am not sure that they
ever did.  I want to make the following change just so that I don't keep
getting the usage message.  The script completes anyway so it doesn't
seem critical but if anyone has a better way to do this test I will do
that instead.

Note that I am talking about trunk.  The existing releases are fine.
This script is only for me to create the tar file.

Index: mktar
===
--- mktar   (revision 1013)
+++ mktar   (working copy)
@@ -24,14 +24,6 @@
  echo "If something has changed, edit MANIFEST.in and mktar."
  exit 1
  fi
-FILES="mktar mkdocs docs tests pg.py pgdb.py pgmodule.c setup.cfg"
-PERMS=`stat --printf="%a" $FILES`
-if [ $? -eq 0 -a "$PERMS" != '755755755755644644644644' ]
-then
-echo "Hmmm.  File permissions are not set properly."
-echo "Use a filesystem with permissions and do a clean checkout first."
-exit 1
-fi

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Release checklist

[Christoph Zwerschke]

Am 19.05.2019 um 14:32 schrieb D'Arcy Cain:> I just did an update and 
two files were modified but I did not see any

> extra files.  What's missing?  Are they still missing?

Now it looks good. 

I had added some files to the Manifest.in that are needed to build the 
docs. They were missing in the tarball but now it's ok.


-- Christoph






___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Release checklist

[Christoph Zwerschke]

Am 17.05.2019 um 22:16 schrieb D'Arcy Cain:

On 5/17/19 6:52 AM, D'Arcy Cain wrote:

I just rebuilt the site and tar file just in case I missed an update.


I will rebuild again tonight due to the changes just committed.


As far as I see the site has been updated, but the tarballs are still 
old (i.e. missing some files and with older date in changelog).


Can you rebuild the tarballs as well? I will use them as source for 
creating the PyPI relase.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Release checklist

[Christoph Zwerschke]

Am 17.05.2019 um 12:52 schrieb D'Arcy Cain:

I think that we are ready now.  I haven't seen any other issues for a
few days.  Anyone see a reason not to announce?

I just rebuilt the site and tar file just in case I missed an update.


Just noticed a small problem with the source tarballs - they were 
missing some txt files that are needed for building the docs.


Fixed this in the branch, trunk and tags already. Can you create the 
tarballs for 5.0.7 and 5.1 one last time? I'll publish to PyPI then.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] Release checklist

[Christoph Zwerschke]

Am 15.05.2019 um 16:04 schrieb D'Arcy Cain:

We obviously rushed into the release. I wonder if I shouldn't just 
remove the two new tags antil we are sure that everything is OK. I

can put both versions up so that they can be checked on the web

Thanks Justin and D'Arcy for noticing and fixing the changelog.

We should create a release checklist for all the things that need to be 
done before a release, including:


- places where version numbers need to be checked
- places where supported Py/Pg versions need to be updated
- proof-read the changelog
- put release date into the changelog

Minor changes in the readme or changelog can still be made on the tags 
in subversion. Or you can delete the tags and recreate them.


The critical point is when we publish on PyPI which I haven't done yet. 
Once it's uploaded there, we can't make fixes any more. The released 
files have checksums and can be only uploaded once, so we would need to 
create a new version number for any fix.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] PyGreSQL 5.1 ready for release

[Christoph Zwerschke]

Am 13.05.2019 um 17:22 schrieb Justin Pryzby:

Checking back about this.

I'm now running this in production at a few customers, including use of
query_prepared().  In one instance I looked at, this is loading 2.7x faster.


From my side everything is fine too.

@D'Arcy - can you create the release on the server?

The 5.1 tag should be copied from the trunk,
the 5.0.7 tag from the 5.0.x branch.

I will then create the Windows versions and publish on PyPI as well.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

[PyGreSQL] PyGreSQL 5.1 ready for release

[Christoph Zwerschke]

PyGreSQL 5.1 (and 5.0.7) is ready for release now from my side. All 
tests are green for me on Linux and Windows with the supported Python 
and Postgres versions. Let me know if something does not work for you or 
I have forgotten something that should be included in the release.


Thanks to Justin again for the help with this release.

I suggest we let this sit for 1-2 more days and if nobody objects 
publish the release.


The 5.1 tag should be copied from the trunk, the 5.0.7 tag from the 
5.0.x branch.


-- Christoph







___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [980] trunk: Query methods for getting single rows and columns

[Christoph Zwerschke]

Am 22.04.2019 um 21:15 schrieb Justin Pryzby:
> I suggest that single() should return None for n==0,
> and MultipleResultsFoun> for n>1.

Actually I already added a one() method that is sloppy and returns None. 
The single() method is the opposite and considered to be very strict, 
when you really expect one and only one value from the database.


In SQLALchemy, one() is called first() and single() is called one(), 
which is a bit unfortunate, but in SQLAlchemy the latter also throws 
when there is no value.


I chose the name one() instead of first() to be more in line with pgdb 
and because it can be called multiple times to get the following rows, 
so that the name first() was not appropriate.


single() also corresponds in name and semantics to the Single() method 
in LINQ which also throws if not exactly one row.


I really don't want to introduce a third method that throw only if you 
have more than 1 result, but not for 0 results.


> If you wanted to be really extreme you could have THREE subclasses...
> |class NotOnlyOne(DataError):pass
> |class MoreThanOne(NotOnlyOne):pass
> |class LessThanOne(NotOnlyOne):pass

Yes, a bit extreme, but necessary to avoid the need for catching 2 
different errors. Maybe the top level should just be InvalidResult 
instead of NotSingleResult or NotOnlyOne?


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] [980] trunk: Query methods for getting single rows and columns

[Christoph Zwerschke]

Am 22.04.2019 um 20:35 schrieb Justin Pryzby:
> Isn't it a best-practice to make a subclass of ProgrammingError just

Maybe in this case also better a subclass of DataError?

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] pg.DB() pseudo-cursor to reduce RAM use

[Christoph Zwerschke]

For the records, this feature ("iterators as pseudo-cursors") has now 
finally been committed in r978 and will be available in PyGres 5.1.


Thanks to Justin for the discussion and implementation. I have only made 
a few minor changes and added some missing methods for dicts and named 
tuples, tests and docs.


-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] PyGreSQL #77: Bad performance of query_formatted()

[Christoph Zwerschke]

Am 20.04.2019 um 01:20 schrieb Justin Pryzby:

  Also, caching the types of the values works
  only if you have very few different values (like in your case only a
  zero), but for most real-world examples with different strings or floats
  this will not work.


Do you mean "..works only if you have very few different TYPES OF values" ?


Sorry, it was too late and somehow I thought you were using a mapping 
from values to types. That would be a very bad idea as the range of 
values is infinite and some may not even been hashable.


But of course you were using a mapping from Python types to PyGres 
simple types. I've changed the code to use such a mapping for the most 
frequent types, too. I'm not using a self-extending mapping like 
_SimpleTypes() though, because for complex types, it would not work - 
e.g. the values [1] and ['x'] have the same Python type "list" but 
should map to the different PyGres simple types "int[]" and "text[]".


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] query formatting is nearly 10x slower than query

[Christoph Zwerschke]

Am 19.01.2019 um 18:24 schrieb Justin Pryzby:

1) in 5.0, document that relative to query, query_formatted has an overhead
"which can be significant for queries repeated many times", and document that
the mitigation is to use inline=True; or, use prepared statements "available
since 5.1".  Note that for simple queries like INSERT, the significant overhead
is in pygres, but for complex queries like JOINs/large inheritence trees/etc,
the more overhead is in planning.


Just for the records: This has been treated in 
http://trac.pygresql.org:8000/pgtracker/ticket/77


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] should dictresult warn if column name is reused ?

[Christoph Zwerschke]

Am 21.01.2019 um 22:41 schrieb Justin Pryzby:
> -- There are two columns here:
> pg.DB('ts').query('SELECT x.b, y.a AS b FROM (SELECT 1 AS b )x
> JOIN(SELECT 1 AS a)y ON true').getresult()> [(1, 1)]
>
> -- But here they're silently "collapsed" into one key:
> pg.DB('ts').query('SELECT x.b, y.a AS b FROM (SELECT 1 AS b )x
> JOIN(SELECT 2 AS a)y ON true').dictresult()> [{'b': 2}]
>
> ..which is scary to me since nobody knows what they're going to get,
> or that same value is being used for two different things instead of
> different values..

Well, the behavior is deterministic - you always get the column with the 
highest index.


If you request a dict, then it's not reasonable to expect anything else.
Note that if you do a similar thing in Python, you don't get a warning 
either:


dict([('b', 1), ('b', 2)]) = {'b': 2}

> If this is agreed to be a problem, maybe write warning to stderr in
> 5.0 and fail harder in 5.1+ ?

If anything, then there should be a Python warning, as it's not 
considered good practice for a library or driver to print on stderr.


But as D'Arcy already commented, there should be only a warning when the 
values differ. However, detecting this would cost too much performance. 
So there are good reasons to leave things as they are.


Maybe we can just add a note to the docs for dictresult() that column 
names are assumed to be unique.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] use_regtypes

[Christoph Zwerschke]

Am 12.01.2019 um 17:58 schrieb Justin Pryzby:

I realized that the pygres documentation talking about "regular types" is
actually referring to ::regtype, which I think (?) might actually stand for
"registered type" (reg class, regrole, etc).


Right, regtype probably stands for "registered type" in PostgreSQL. Our 
docs say "regular" to  distinguish these "official" PostgreSQL types 
from the inofficial, and much more coarse classical PyGreSQL types.


To clarify, we could distinguish between

* the (generalized) traditional PyGreSQL type names
* the (more exact) registered PostgreSQL type names

If you like, send a patch for the docs.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] proofread docs

[Christoph Zwerschke]

Thanks for the heads up, Justin. It's fixed now.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] proofread docs

[Christoph Zwerschke]

Am 05.01.2019 um 17:42 schrieb Justin Pryzby:

On Fri, Jan 04, 2019 at 11:44:47PM +0100, Christoph Zwerschke wrote:
There's a few remaining chunks which I think are improvements.  Please review
and apply your best judgement and standards :)


Ok, thank you. See last commit message.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] "TypeError: Connection is not valid" on exit from Python

[Christoph Zwerschke]

Am 03.01.2019 um 18:10 schrieb Jacob Champion:
> Bump. Is anyone else able to reproduce this exit exception, and does
> the proposed fix make sense?

Thank you Jacob.

I have created a GitHub issue here and will look into this later:
https://github.com/Cito/PyGreSQL/issues/11

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 21:44 schrieb Justin Pryzby:> On Fri, Jan 04, 2019 at 
08:47:38PM +0100, Christoph Zwerschke wrote:

>> Next wormhole will be the iterator support :)
>
Just curious, are you planning to include iterators in 5.1 or 5.2 ? 
I'm hoping to put prepared statements in place within the next month

or so, and considered including a patched pygres with our server
application if need be (but I'd prefer not to).
In the spirit of RERO I think we should get 5.1 out soon (in 1-2 weeks) 
and then put iterators in 5.2 or 6 depending on how breaking the change 
will be. We should also publish the 5.0.7 bugfix relase and then 
continue to release bugfixes only in the 5.1 branch.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] proofread docs

[Christoph Zwerschke]

Am 04.01.2019 um 22:00 schrieb Justin Pryzby:
> I proofread the docs.   PFA patch with suggested changes.

Thanks, that's greately appreciated. Will push this into the repository.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 20:08 schrieb Justin Pryzby:
> On Fri, Jan 04, 2019 at 07:42:28PM +0100, Christoph Zwerschke wrote:
>> Yes, I think this is the case. And I got the impression that even
>> simple PQexecs annihilate existing unnamed statements. Did you also
>> see that?
>
> I'll check more shortly.
>
> Sorry for leading you down this wormhole..

At least we now have proper prepared statement support in PyGreSQL.

Next wormhole will be the iterator support :)

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 19:10 schrieb Justin Pryzby:

Currently it calls PQexecParams which (I think you agree) is creating unnamed
prepared statements behind the scenes.


Just found this in the docs
(https://www.postgresql.org/docs/11/protocol-flow.html):

"An unnamed prepared statement lasts only until the next Parse statement 
specifying the unnamed statement as destination is issued. (Note that a 
simple Query message also destroys the unnamed statement.)"


The side node is exactly what I observed. As far as I understand, this 
means unnamed prepared statements should be only executed once, and best 
directly after they have been created.


We could add this as caveat to the docs and docstrings.

And I will also revert to the API with the name as a positional 
parameter, since unnamed statements are not so useful and the API is 
then more consistent and more convenient for named statements.


-- Christoph

___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 19:10 schrieb Justin Pryzby:
> But instead should pygres use NAMED prepared statements for its
> internal queries, to avoid its unnamed queries conflicting with
> users' unnamed prepared> statements ?

I fear that would open a whole new can of worms for us. We would need 
our own namespace for the names, and keep track of the lifetime of these 
statements, particularly when they need to be created (remember they 
live only one session long). Since we don't have control over what the 
user is in the meantime, we would probably need to test every time if 
the statement is still there. A lot of overhead...


> Currently it calls PQexecParams which (I think you agree) is creating
> unnamed prepared statements behind the scenes.

Yes, I think this is the case. And I got the impression that even simple 
PQexecs annihilate existing unnamed statements. Did you also see that?


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 22:52 schrieb Justin Pryzby:> One thought ; should 
"name" be an kwarg with default name='' ?


It's now implemented that way. But I noticed an issue with unnamed 
queries. It seems like Postgres replaces the unnamed query as soon as 
it's running any other query. That means any query that the DB wrapper 
is running behind the scenes (e.g. to fetch types) or you are running in 
your code between query_prepared() calls will destroy the unnamed query.


So this makes unnamed query really not that useful and we should 
discourage using them. The question is then, should we not revert that 
change and use a positional argument for the name again. That's more 
convenient than a keyword-only argument if you need to set it anyway 
every time.


-- Christoph


___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 03:07 schrieb Justin Pryzby:
> On Fri, Jan 04, 2019 at 12:26:59AM +0100, Christoph Zwerschke wrote:
>> The question is now - should we use this different signature only
>> in the DB wrapper method or already in the underlying method?
>
> I don't have any opinion on this.

Ok, so I decided to leave it like that. The low-level API is now a bit 
different from the DB wrapper API. That actually was already the case 
for other methods like query() which takes individual arguments as 
wrapper method and a list as low-level method. People should only use 
the wrapper class anyway.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 04.01.2019 um 00:08 schrieb Justin Pryzby:
> I suggest it should actually be:
>
> |+def query_prepared(self, *args, **kwargs):
> |+def prepare(self, command, **kwargs):
>
> name=kwargs.get('name', '')
>
> So one *has* to pass name as named param.

For prepare() we don't need that trick. But for query_prepared, yes, 
that looks like a feasible solution. You can now leave the name out, but 
if you want one, you need to pass it as named param.


The question is now - should we use this different signature only in the 
DB wrapper method or already in the underlying C extension method?


The latter would be a little bit more complicated, but less confusing 
when people are using the raw connection.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 22:58 schrieb Christoph Zwerschke:

Am 03.01.2019 um 22:52 schrieb Justin Pryzby:

>>

One thought ; should "name" be an kwarg with default name='' ?


Yes, that's a good idea. The parameters would then go in opposite order 
to the underlying libpq method, but it would be more pythonic.


So I have now made the name optional. But I'm not quite sure any more if 
we really want it that way. There are two drawbacks:


1. The order of params is now different from the underlying C function 
where the name comes first which could be confusing.


2. People might be tempted to leave out the name when calling 
query_formatted() with arguments, but then the first argument would be 
interpreted as the name. This may cause unexpected errors.


So maybe forcing the name to always be the first, non-optional parameter 
might be less confusing for users.


What do you think? Revert or leave it like that?

-- Christoph





___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 23:40 schrieb Justin Pryzby:
> Just a nitpick:
>
> + If you pass no parameters or pass an empty name, then the last unnamed
> + statement will be executed.
>
> + the prepared statement with the given name. If you do not specify a
> + name, then the last unnamed statement will be described.
>
> I found the word "last" to be confusing here.
>
> I think postgres simply refers to it as "the unnamed statement", or 
similar.
There can be only one unnamed statement at a time, but you can use two 
different unnamed statements after the other in one session. You can 
also have no unnamed statement prepared in your session. That's why I 
found speaking of "the unnamed statement" here also a bit confusing.


Check out my new formulation.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 23:12 schrieb Justin Pryzby:
> I agree at least that there should be SOME method accepting $1
> for prepared query, and that it's more important than a method
> for using %s.
> And it's more important for us that there be some method at all
> than that it uses %s.  :)

Right, we later can still add a method prepare_formatted() or add a 
keyword parameter to prepare() for switching the param style.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 22:52 schrieb Justin Pryzby:
> On Thu, Jan 03, 2019 at 09:49:24PM +0100, Christoph Zwerschke wrote:
> |+def query_prepared(self, name, *args):
> |+def prepare(self, name, command):
>
> One thought ; should "name" be an kwarg with default name='' ?

Yes, that's a good idea. The parameters would then go in opposite order 
to the underlying libpq method, but it would be more pythonic.


-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] prepared statements

[Christoph Zwerschke]

Am 03.01.2019 um 21:58 schrieb Justin Pryzby:
> On Thu, Jan 03, 2019 at 09:49:24PM +0100, Christoph Zwerschke wrote:
>> db.prepare('insert-tweet', "INSERT INTO tweets VALUES($1, $2, $3)")
>
> My first thought is to ask why not use %s parameters ?

Because the $1 form is also the syntax for parameters used by the 
classic module in the query() method. It's very simple and natural 
because it's the same as you create the prepared statement in plain SQL. 
And it allows to adapt the query to the order of the parameter values.


Only the special query_formatted() method uses %s parameters.

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql

Re: [PyGreSQL] typos

[Christoph Zwerschke]

Am 02.01.2019 um 17:17 schrieb D'Arcy Cain:
> On 1/2/19 8:30 AM, Justin Pryzby wrote:
>> This isn't just a typo, isn't this wrong??
>> days += 365 * years + 30 * mons
> It's certainly inexact.  How would you fix that given that we don't
> have the start date?

The problem here is that this function needs to cast Postgres intervals 
to Python timedeltas. While Postgres intervals have years and months, 
Python timedeltas have only days.


So this sems to be the best conversion we can do here.

Btw, in some cases, Postgres also assumes 1 month = 30 days, e.g.

> select '0.5 months'::interval
15 days

-- Christoph
___
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql