Re: [GENERAL] Postgres, apps, special characters and UTF-8 encoding

[vinny]

On 2017-03-08 00:20, Ken Tanzer wrote:

Hi.  I've got a recurring problem with character encoding for a
Postgres-based web PHP app, and am hoping someone can clue me in or at
least point me in the right direction.  I'll confess upfront my
understanding of encoding issues is extremely limited.  Here goes.




  And that one way or another, the encoding
needs to be translated before it can be placed into the database.



Ken

--



You don't really have to translate the encoding, because all parts of 
the system

are capable of dealing with all encodings.

What you have to make sure that that they are indeed all working in the 
same encoding.
You have to set the encoding of the HTML document, the database, and the 
database connection
to the same encoding, like utf8. People tend to forget the "set names" 
on the database connection,
which can make the database think you are sending latin1, but you are 
really sending utf-8, and presto problemo.


Then the only problem left is that PHP doesn't do utf-8 very well 
internally
 so if you receive data from an UTF-8 page and want to substring etc 
then you have to use the multibyte variants
of those functions. You could convert everything back to latin1 first, 
but then

you might as well just do everything in latin1 in the first place.


--
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] Request to confirm which command is use for exclusive operation

[Scott Marlowe]

On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe  wrote:
> On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  wrote:
>> Dear David,
>>
>>  I want to apply explicitly lock mechanism once inset operation is in
>> progress then REINDEX will wait.
>> And vice versa.
>> So, please let me know this type of handling is possible.
>> Regrds,
>> Yogesh
>
> Create two roles grant / revoke permissions as needed. maybe
> pg_stat_activity for locks etc

Also you could have a table with a simple token in it etc. active
process gets token, all other processes wait on it.


-- 
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] Request to confirm which command is use for exclusive operation

[Scott Marlowe]

On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  wrote:
> Dear David,
>
>  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.
> Regrds,
> Yogesh

Create two roles grant / revoke permissions as needed. maybe
pg_stat_activity for locks etc


-- 
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] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear David,

 I want to apply explicitly lock mechanism once inset operation is in
progress then REINDEX will wait.
And vice versa.
So, please let me know this type of handling is possible.
Regrds,
Yogesh

On Wednesday, March 8, 2017, David G. Johnston 
wrote:

> On Tuesday, March 7, 2017, Yogesh Sharma  > wrote:
>
>> Dear all,
>>
>> Thanks for your support.
>>
>> I need to perfrom INSERT and REINDEX operation exclusively.
>> For example:
>> If REINDEX operation is in progress then INSERT operation will wait  and
>> vice versa.
>>
>> Please let me know if any approach is available.
>>
>>
> The docs describe REINDEX locking mechanics.
>
> https://www.postgresql.org/docs/current/static/sql-reindex.html
>
> That a reindex blocks writes implies the reverse, any writes in progress
> will prevent the reindex from starting.
>
> David J.
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Tuesday, March 7, 2017, Yogesh Sharma  wrote:

> Dear all,
>
> Thanks for your support.
>
> I need to perfrom INSERT and REINDEX operation exclusively.
> For example:
> If REINDEX operation is in progress then INSERT operation will wait  and
> vice versa.
>
> Please let me know if any approach is available.
>
>
The docs describe REINDEX locking mechanics.

https://www.postgresql.org/docs/current/static/sql-reindex.html

That a reindex blocks writes implies the reverse, any writes in progress
will prevent the reindex from starting.

David J.

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/07/2017 08:17 PM, John Iliffe wrote:

I was unable to get postgres started so I did a very basic compile/install
to test it.  The configuration line was:

./configure --prefix=/usr/postgres-9.6.2


Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--
Then make check

make check

===
 All 167 tests passed.
===

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'


Then make install as user root because of directory permissions.  I have
the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a
number of files as expected.  As yet no database has been defined because
psql won't start.


Just to be clear you installed in:

/usr/postgres-9.6.2

but created the data directory in:

Also above you say:

"I was unable to get postgres started so I did a very basic 
compile/install to test it. "


To me that implies there is another instance of Postgres on the system, 
is that the case?


If not could you explain what you meant?



The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Same results if I use pg_ctl to start the process.

I checked with lsof and there is no process bound to socket 5432.  There is
no entry in /var/run for a socket related to postgresql.

I thought it might be a security issue so I put SELinux in permissive mode
but the result is the same.  The SELinux journal does not show any warnings
on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount:/sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode:   permissive  <--permissive mode**
Mode from config file:  permissive
Policy MLS status:  enabled
Policy deny_unknown status: allowed
Max kernel policy version:  30
-

One thing that I haven't been able to find any the log files.  Where are they
normally stored?

So, any ideas as to where to go next to debug this would be appreciated!
This is a brand new server that I am trying to configure so I have a fair
amount of security clearance to chase things.

Thanks in advance.

John
=







--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Unable to start postgresql

[Tom Lane]

John Iliffe  writes:
> Now, running as user postgres I try and start as stated in the manual
> postgres -D /usr/pgsql_tablespaces

> The result is:
> [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
> LOG:  could not bind IPv4 socket: Cannot assign requested address
> HINT:  Is another postmaster already running on port 5432? If not, wait a 
> few seconds and retry.
> LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> LOG:  MultiXact member wraparound protections are now enabled
> LOG:  database system is ready to accept connections
> LOG:  autovacuum launcher started

To clarify: the postmaster *is* starting here.  It failed to bind to the
IPv4 port 5432, but it must have succeeded in binding to at least one
other port (IPv6 and/or a Unix socket), else it would have stopped and
you'd have not seen the last four log lines.

It might be helpful to check with lsof to see what the postmaster process
has open after you do this.

> I checked with lsof and there is no process bound to socket 5432.  There is 
> no entry in /var/run for a socket related to postgresql.

With the default configure options you used, the postmaster would have put
its Unix socket file into /tmp, not /var/run.  I wonder whether your
problem is that you're trying to connect to it with distro-supplied
psql+libpq that expects to find the Unix socket in /var/run.

> One thing that I haven't been able to find any the log files.  Where are they 
> normally stored?

They seem to be showing up on postmaster's stderr (ie, your terminal)
which again is the vanilla-configuration default if you didn't do anything
to edit the postgresql.conf settings.

I suspect that you're used to the behavior of a vendor-configured postgres
package and have not taken the steps needed to make a build from source
behave the same way.  Recommend looking into what patches the vendor
package applies and what configure options are used.

Having said all that, it's very un-obvious why you're failing to bind
to the IPv4 socket.  If there's no active postmaster on the machine,
that should be free.  I could believe SELinux blocking it, except that
the targeted SELinux policy shouldn't constrain a manually-started
postmaster at all --- not to mention that you're in permissive mode.
Seems like the answer must be elsewhere.

You didn't mention what platform you're on, but the reference to SELinux
makes me think it's probably Red Hat.  A bit of digging in RH's support
portal turns up a few mentions of kernel bugs causing unexpected
EADDRNOTAVAIL errors, which matches this symptom ... so how up-to-date
is this server?

regards, tom lane


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

[GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear all,

Thanks for your support.

I need to perfrom INSERT and REINDEX operation exclusively.
For example:
If REINDEX operation is in progress then INSERT operation will wait  and
vice versa.

Please let me know if any approach is available.

Regards,
Yogesh sharma

Re: [GENERAL] Unable to start postgresql

[Melvin Davidson]

On Tue, Mar 7, 2017 at 11:17 PM, John Iliffe  wrote:

> I was unable to get postgres started so I did a very basic compile/install
> to test it.  The configuration line was:
>
> ./configure --prefix=/usr/postgres-9.6.2
>
> 
> Then I ran make which completed as expected:
>
> make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
> All of PostgreSQL successfully made. Ready to install.
>
> --
> Then make check
>
> make check
>
> ===
>  All 167 tests passed.
> ===
>
> make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
> 
>
> Then make install as user root because of directory permissions.  I have
> the expected files in /usr/postgres_9.6.2
>
> Now, running as user postgres I try and start as stated in the manual
>
> postgres -D /usr/pgsql_tablespaces
>
> initdb has already been run and the directory pgsql_tablespaces has a
> number of files as expected.  As yet no database has been defined because
> psql won't start.
>
> The result is:
> [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
> LOG:  could not bind IPv4 socket: Cannot assign requested address
> HINT:  Is another postmaster already running on port 5432? If not, wait a
> few seconds and retry.
> LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> LOG:  MultiXact member wraparound protections are now enabled
> LOG:  database system is ready to accept connections
> LOG:  autovacuum launcher started
>
> Same results if I use pg_ctl to start the process.
>
> I checked with lsof and there is no process bound to socket 5432.  There is
> no entry in /var/run for a socket related to postgresql.
>
> I thought it might be a security issue so I put SELinux in permissive mode
> but the result is the same.  The SELinux journal does not show any warnings
> on this process.
>
> [root@prod04 postgresql-9.6.2]# sestatus
> SELinux status: enabled
> SELinuxfs mount:/sys/fs/selinux
> SELinux root directory: /etc/selinux
> Loaded policy name: targeted
> Current mode:   permissive  <--permissive mode**
> Mode from config file:  permissive
> Policy MLS status:  enabled
> Policy deny_unknown status: allowed
> Max kernel policy version:  30
> -
>
> One thing that I haven't been able to find any the log files.  Where are
> they
> normally stored?
>
> So, any ideas as to where to go next to debug this would be appreciated!
> This is a brand new server that I am trying to configure so I have a fair
> amount of security clearance to chase things.
>
> Thanks in advance.
>
> John
> =
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

This is very suspicicious.

>LOG:  could not bind IPv4 socket: Cannot assign requested address
>HINT:  Is another postmaster already running on port 5432? If not, wait a
f>ew seconds and retry.

So check to see if the file "postmaster.pid" exists.
If it does, and postgres is NOT running, just delet or rename it and try to
start.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Unable to start postgresql

[John Iliffe]

I was unable to get postgres started so I did a very basic compile/install 
to test it.  The configuration line was:

./configure --prefix=/usr/postgres-9.6.2


Then I ran make which completed as expected:

make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
All of PostgreSQL successfully made. Ready to install.

--
Then make check

make check

===
 All 167 tests passed. 
===

make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'


Then make install as user root because of directory permissions.  I have 
the expected files in /usr/postgres_9.6.2

Now, running as user postgres I try and start as stated in the manual

postgres -D /usr/pgsql_tablespaces

initdb has already been run and the directory pgsql_tablespaces has a 
number of files as expected.  As yet no database has been defined because 
psql won't start.

The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a 
few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Same results if I use pg_ctl to start the process.

I checked with lsof and there is no process bound to socket 5432.  There is 
no entry in /var/run for a socket related to postgresql.

I thought it might be a security issue so I put SELinux in permissive mode 
but the result is the same.  The SELinux journal does not show any warnings 
on this process.

[root@prod04 postgresql-9.6.2]# sestatus
SELinux status: enabled
SELinuxfs mount:/sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode:   permissive  <--permissive mode**
Mode from config file:  permissive
Policy MLS status:  enabled
Policy deny_unknown status: allowed
Max kernel policy version:  30
-

One thing that I haven't been able to find any the log files.  Where are they 
normally stored?

So, any ideas as to where to go next to debug this would be appreciated!  
This is a brand new server that I am trying to configure so I have a fair 
amount of security clearance to chase things.

Thanks in advance.

John
=




-- 
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] Postgres, apps, special characters and UTF-8 encoding

[rob stone]

Hi Ken,

On Tue, 2017-03-07 at 15:20 -0800, Ken Tanzer wrote:
> Hi.  I've got a recurring problem with character encoding for a
> Postgres-based web PHP app, and am hoping someone can clue me in or
> at least point me in the right direction.  I'll confess upfront my
> understanding of encoding issues is extremely limited.  Here goes.
> 
> The app uses a Postgres database, UTF-8 encoded.  Through their
> browsers, users can add and edit records often including text.  Most
> of the time this works fine.  Though sometimes this will fail with
> Postgres complaining, for example, "Could query with ... , The error
> text was: ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x20
> 0x67"
> 
> So this generally happens when people copy and paste things out of
> their word documents and such.
> 
> As I understand it, those are likely encoded in something non-UTF-8,
> like WIN-1251 or something.  And that one way or another, the
> encoding needs to be translated before it can be placed into the
> database.  I'm not clear how this is supposed to happen though. 
> Automatically by the browser?  Done in the app?  Some other way?  And
> if in the app, how is one supposed to know what the incoming encoding
> is?
> 
> Thanks in advance for any help or pointers.
> 
> Ken
> 
> 
> 

1) Make sure the text editor you use to create your pages, etc. uses
UTF-8 as its encoding. That way the file's BOM is set correctly.
2) Make sure your headers contain the following:-



or 

 which is HTML5, however the long version is
still recognised by HTML5.

I understand that some versions of IE have issues with correctly
determining the character set, so, unfortunately, you still have to
verify that user input is UTF-8 compatible.

HTH,
Rob 


-- 
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] Postgres, apps, special characters and UTF-8 encoding

[Adrian Klaver]

On 03/07/2017 03:20 PM, Ken Tanzer wrote:

Hi.  I've got a recurring problem with character encoding for a
Postgres-based web PHP app, and am hoping someone can clue me in or at
least point me in the right direction.  I'll confess upfront my
understanding of encoding issues is extremely limited.  Here goes.

The app uses a Postgres database, UTF-8 encoded.  Through their
browsers, users can add and edit records often including text.  Most of
the time this works fine.  Though sometimes this will fail with Postgres
complaining, for example, "Could query with ... , The error text was:
ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x20 0x67"

So this generally happens when people copy and paste things out of their
word documents and such.

As I understand it, those are likely encoded in something non-UTF-8,
like WIN-1251 or something.  And that one way or another, the encoding
needs to be translated before it can be placed into the database.  I'm
not clear how this is supposed to happen though.  Automatically by the
browser?  Done in the app?  Some other way?  And if in the app, how is
one supposed to know what the incoming encoding is?


I don't use PHP, but found this:

http://www.php.net/manual/en/function.mb-detect-encoding.php

and this:

http://php.net/manual/en/function.mb-convert-encoding.php




Thanks in advance for any help or pointers.

Ken


--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://agency-software.org/demo/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Postgres, apps, special characters and UTF-8 encoding

[David G. Johnston]

On Tue, Mar 7, 2017 at 4:20 PM, Ken Tanzer  wrote:

> As I understand it, those are likely encoded in something non-UTF-8, like
> WIN-1251 or something.  And that one way or another, the encoding needs to
> be translated before it can be placed into the database.  I'm not clear how
> this is supposed to happen though.  Automatically by the browser?  Done in
> the app?  Some other way?  And if in the app, how is one supposed to know
> what the incoming encoding is?
>

​Haven't run into this problem personally, probably accidentally lucky, but
ISTM that you need to tell the browser what character set you are working
in.  It is in the best position to mediate between the user and the server.

Ideally, on the server, you can examine HTTP headers to learn about the
incoming data charset/encoding (I may not be using these terms precisely
but you should get the idea).

Googling "html input field encoding" seems to provide a decent start.

Note that technically the data encoding issues can occur without HTML, its
really an HTTP layer thing, but the medium of use you care about is
HTTP/Browsers.

David J.

[GENERAL] Postgres, apps, special characters and UTF-8 encoding

[Ken Tanzer]

Hi.  I've got a recurring problem with character encoding for a
Postgres-based web PHP app, and am hoping someone can clue me in or at
least point me in the right direction.  I'll confess upfront my
understanding of encoding issues is extremely limited.  Here goes.

The app uses a Postgres database, UTF-8 encoded.  Through their browsers,
users can add and edit records often including text.  Most of the time this
works fine.  Though sometimes this will fail with Postgres complaining, for
example, "Could query with ... , The error text was: ERROR: invalid byte
sequence for encoding "UTF8": 0xe9 0x20 0x67"

So this generally happens when people copy and paste things out of their
word documents and such.

As I understand it, those are likely encoded in something non-UTF-8, like
WIN-1251 or something.  And that one way or another, the encoding needs to
be translated before it can be placed into the database.  I'm not clear how
this is supposed to happen though.  Automatically by the browser?  Done in
the app?  Some other way?  And if in the app, how is one supposed to know
what the incoming encoding is?

Thanks in advance for any help or pointers.

Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] PGSQL 9.6.2 unable to find readline

[Adrian Klaver]

On 03/07/2017 01:33 PM, John Iliffe wrote:

Perfect!   Thank you.

Same issue then occurred with zlib and I installed the dev pieces and it
worked too.

Might be a good idea if the documentation listed these somewhere as
prereqs.  In the past all the servers I have worked on had these installed


https://www.postgresql.org/docs/9.6/static/install-requirements.html


already but this one is a bit basic.

Regards, and again, thanks.

John
===
On Tuesday 07 March 2017 16:12:22 Devrim Gündüz wrote:

Hi,

On Tue, 2017-03-07 at 15:38 -0500, John Iliffe wrote:

Trying to compile pgsql 9.6.2 on Fedora 25

I get the following message:

configure:9345: error: readline library not found


Please install readline-devel.

(BTW, https://yum.PostgreSQL.org has 9.6.2 and Fedora 25 RPMs already)

Regards,






--
Adrian Klaver
adrian.kla...@aklaver.com


--
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 TAP regression tests under windows/msvc

[Mark Dilger]

> On Mar 7, 2017, at 12:24 PM, Mark Dilger  wrote:
> 
> Hello,
> 
> I am attempting to get the tap tests working under windows so as to
> help review patches for the 10.0 development cycle.  I can compile
> the sources on windows 2008 using the MS Visual C and run the
> 
>   vcregress.bat check
> 
> without any problems or failures.  In an attempt to run the tap tests,
> I have edited config_default.pl as follows:
> 
> diff --git a/src/tools/msvc/config_default.pl 
> b/src/tools/msvc/config_default.pl
> index 97f1af8..1e7b19d 100644
> --- a/src/tools/msvc/config_default.pl
> +++ b/src/tools/msvc/config_default.pl
> @@ -16,7 +16,7 @@ our $config = {
>extraver  => undef,# --with-extra-version=
>gss   => undef,# --with-gssapi=
>nls   => undef,# --enable-nls=
> -   tap_tests => undef,# --enable-tap-tests
> +   tap_tests => 1,# --enable-tap-tests
>tcl   => undef,# --with-tls=
>perl  => undef,# --with-perl
>python=> undef,# --with-python=
> 
> and when I run
> 
>   vcregress.bat bincheck
> 
> I get a few failures.  Am I doing something wrong, or are these failures
> the same for other folks?  A portion of the log of the regressions follows:

I added a bit of debugging logic to PostgresNode.pm, to print out the
name of the log file being grep'd and the size of that file, and it seems
in these cases the log file is of size zero (empty).

not ok 10 - SQL CLUSTER run: SQL found in server log 
c:/jenkins/workspace/unicorns/postgresql/src/bin/scripts/tmp_check/log/010_clusterdb_main.log,
 length 0
ok 11 - fails with nonexistent table
ok 12 - clusterdb -t test1 exit code 0
not ok 13 - cluster specific table: SQL found in server log 
c:/jenkins/workspace/unicorns/postgresql/src/bin/scripts/tmp_check/log/010_clusterdb_main.log,
 length 0
ok 14 - clusterdb with connection string
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/14 subtests 

Perhaps there is a race condition between when the test is run and when the
log file is flushed?  I'm just guessing here

-- 
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] PGSQL 9.6.2 unable to find readline

[John Iliffe]

Perfect!   Thank you.

Same issue then occurred with zlib and I installed the dev pieces and it 
worked too.

Might be a good idea if the documentation listed these somewhere as 
prereqs.  In the past all the servers I have worked on had these installed 
already but this one is a bit basic.

Regards, and again, thanks.

John
===
On Tuesday 07 March 2017 16:12:22 Devrim Gündüz wrote:
> Hi,
> 
> On Tue, 2017-03-07 at 15:38 -0500, John Iliffe wrote:
> > Trying to compile pgsql 9.6.2 on Fedora 25 
> > 
> > I get the following message:
> > 
> > configure:9345: error: readline library not found
> 
> Please install readline-devel.
> 
> (BTW, https://yum.PostgreSQL.org has 9.6.2 and Fedora 25 RPMs already)
> 
> Regards,


-- 
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] PGSQL 9.6.2 unable to find readline

[Devrim Gündüz]

Hi,

On Tue, 2017-03-07 at 15:38 -0500, John Iliffe wrote:
> Trying to compile pgsql 9.6.2 on Fedora 25 
> 
> I get the following message:
> 
> configure:9345: error: readline library not found

Please install readline-devel.

(BTW, https://yum.PostgreSQL.org has 9.6.2 and Fedora 25 RPMs already)

Regards,
-- 
Devrim Gündüz
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Feature request - psql --quote-variable

[David G. Johnston]

On Tue, Mar 7, 2017 at 1:29 PM, Pavel Stehule 
wrote:

>
>
> 2017-03-07 21:04 GMT+01:00 Caleb Cushing :
>
>> Thank you. Apparently I never saw this response, for some reason...
>>
>> So reading that leaves me confused on one point, which is the right way
>> to do it if you're inserting an  integer? would this be right? is there a
>> difference between the single and double quotes here?
>>
>
> postgres=# create table foo(a int);
> CREATE TABLE
> Time: 276,386 ms
> postgres=# insert into foo values('1');
> INSERT 0 1
> Time: 72,357 ms
>
>
>
>>
>> (presume id is a bigint)
>> `insert into foo ( id ) values ( :'var' )`
>>
>
> double quotes are used for identifiers. '' is string literal, ""
> is sql identifier like table name or column name.
>
>
​This is a bit of cheating since the system, knowing that "a" is of type
"int", is allowed to implicitly cast an unadorned/untyped literal '1'​.

What is really happening is:

insert into foo (a) values ('1'::integer);

IOW - it is OK - and cheap - to place integers into single quotes and then
cast them in order to add anti-injection features to the query.

Dave

[GENERAL] PGSQL 9.6.2 unable to find readline

[John Iliffe]

Trying to compile pgsql 9.6.2 on Fedora 25 

I get the following message:

configure:9345: error: readline library not found
If you have readline already installed, see config.log for details on the
Use --without-readline to disable readline support.
pgac_cv_check_readline=no

libreadline does exist:

[John@prod04 postgresql-9.6.2]$ rpm -qv readline
readline-6.3-8.fc24.x86_64

Other relevant info:
gcc 6.3.1


command line:
../configure --prefix=/usr/postgres-9.6.2 --with-openssl --with-
libraries=/usr/lib64


libreadline is in /usr/lib64

ls -l /usr/lib64/libread*
lrwxrwxrwx. 1 root root 18 Feb  4  2016 /usr/lib64/libreadline.so.6 -> 
libreadline.so.6.3
-rwxr-xr-x. 1 root root 296072 Feb  4  2016 /usr/lib64/libreadline.so.6.3


This is similar to other posts regarding pgsql 9.3 in the archive but has 
anyone found the cause yet?  

Thanks.

John


-- 
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] Feature request - psql --quote-variable

[Pavel Stehule]

2017-03-07 21:04 GMT+01:00 Caleb Cushing :

> Thank you. Apparently I never saw this response, for some reason...
>
> So reading that leaves me confused on one point, which is the right way to
> do it if you're inserting an  integer? would this be right? is there a
> difference between the single and double quotes here?
>

postgres=# create table foo(a int);
CREATE TABLE
Time: 276,386 ms
postgres=# insert into foo values('1');
INSERT 0 1
Time: 72,357 ms



>
> (presume id is a bigint)
> `insert into foo ( id ) values ( :'var' )`
>

double quotes are used for identifiers. '' is string literal, "" is
sql identifier like table name or column name.


>
> maybe the docs should mention sql injection? (if for nothing more than
> google indexing and ctrl+f page searching)
>

This is psql client side feature - where SQL injection is possible, but the
risk is usually low - more significant are errors coming from missing or
wrong value escapeing.

Currently in patch pool is a patch, that enable possibility to use
parametrized queries from psql - it can be another way, how to execute
query safely.

Any documentation enhancing is good. If you have a idea, please, send a
text.

Regards

Pavel


>
>
> On Tue, Feb 21, 2017 at 12:35 PM Pavel Stehule 
> wrote:
>
>> Hi
>>
>> 2017-02-21 15:19 GMT+01:00 Caleb Cushing :
>>
>> recently while exploring this problem http://stackoverflow.com/q/
>> 40945277/206466. I decided to go with the docker container approach of a
>> shell script.
>>
>>  I realized that postgres' variables aren't quoted either, which results
>> in me quoting them with bash, to help avoid accidents, and even then I'm
>> not 100% sure I'm doing it right.
>>
>> #!/bin/bash
>> set -e
>>
>> psql -v ON_ERROR_STOP=1 \
>> -v db="${POSTGRES_DB//\'/''}" \
>> -v user_changeset="${DB_USER_CHANGESET//\'/''}" \
>> -v user_readwrite="${DB_USER_READWRITE//\'/''}" \
>> -v user_readonly="${DB_USER_READONLY//\'/''}" \
>> -v pass_changeset="'${DB_PASS_CHANGESET//\'/''}'" \
>> -v pass_readwrite="'${DB_PASS_READWRITE//\'/''}'" \
>> -v pass_readonly="'${DB_PASS_READONLY//\'/''}'" \
>> --username "${POSTGRES_USER}" \
>> --dbname "${POSTGRES_DB}" \
>> --file="init-user.sql"
>>
>> given the Popularity of Docker and that their are UI's to pass
>> environment variables now (meaning the person doing so might not be a
>> qualified "DBA", nor as trusted as they should be to have "root dba"
>> access).  Even if the person is trusted, I feel like one shouldn't have to
>> document "don't put quotes or SQL into your password" is an indication that
>> something is wrong.
>>
>> It would be nice to have some way to properly have these variables quoted.
>>
>> 1. provide a new argument name say --quote-variable (or -qv) and postgres
>> will figure out how to quote based on the position of the variable
>> 2. allow psql (or another app?) to provide an output quoter (since it has
>> access to the lib) `pql -v user_changeset=$( psql --quote-string
>> $DB_USER_CHANGESET )`, kind of a weird caller but basically allows you to
>> pass an input to a function that does quoting properly
>>
>> there might be other idea's too, these are just the ones I have.
>>
>> Yes I know people who are able to manage such a container should be
>> trusted... in theory though you can provide a UI that gives them access to
>> manage the container with no actual access to the container. I don't
>> actually have that problem it's more of a hypothetical to me, but I'm sure
>> it will exist at some point.
>>
>> Just sharing my pain in hopes that improvements can be developed.
>>
>>
>> [pavel@localhost ~]$ psql
>> Debug assertions "on"
>> psql (10devel)
>> Type "help" for help.
>>
>> postgres=# \set var AHOJ
>> postgres=# \echo :var :'var' :"var"
>> AHOJ 'AHOJ' "AHOJ"
>> postgres=#
>>
>> https://www.postgresql.org/docs/9.2/static/app-psql.html looks to "SQL
>> Interpolation"
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>> p.s. pg is still hashing its passwords with md5? :(
>> --
>> Caleb Cushing
>>
>> http://xenoterracide.com
>>
>> --
> Caleb Cushing
>
> http://xenoterracide.com
>

[GENERAL] Running TAP regression tests under windows/msvc

[Mark Dilger]

Hello,

I am attempting to get the tap tests working under windows so as to
help review patches for the 10.0 development cycle.  I can compile
the sources on windows 2008 using the MS Visual C and run the

vcregress.bat check

without any problems or failures.  In an attempt to run the tap tests,
I have edited config_default.pl as follows:

diff --git a/src/tools/msvc/config_default.pl b/src/tools/msvc/config_default.pl
index 97f1af8..1e7b19d 100644
--- a/src/tools/msvc/config_default.pl
+++ b/src/tools/msvc/config_default.pl
@@ -16,7 +16,7 @@ our $config = {
extraver  => undef,# --with-extra-version=
gss   => undef,# --with-gssapi=
nls   => undef,# --enable-nls=
-   tap_tests => undef,# --enable-tap-tests
+   tap_tests => 1,# --enable-tap-tests
tcl   => undef,# --with-tls=
perl  => undef,# --with-perl
python=> undef,# --with-python=

and when I run

vcregress.bat bincheck

I get a few failures.  Am I doing something wrong, or are these failures
the same for other folks?  A portion of the log of the regressions follows:


All tests successful.
Files=5, Tests=17, 323 wallclock secs ( 0.08 usr +  0.03 sys =  0.11 CPU)
Result: PASS
t/001_pgbench.pl .. 
1..3
ok 1 - concurrent OID generation: exit code 0
ok 2 - concurrent OID generation: no stderr
ok 3 - concurrent OID generation: matches
ok
All tests successful.
Files=1, Tests=3, 12 wallclock secs ( 0.08 usr +  0.03 sys =  0.11 CPU)
Result: PASS

#   Failed test 'SQL CLUSTER run: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^:statement: CLUSTER;)'

#   Failed test 'cluster specific table: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^:statement: CLUSTER test1;)'
# Looks like you failed 2 tests of 14.
t/010_clusterdb.pl  
1..14
ok 1 - clusterdb --help exit code 0
ok 2 - clusterdb --help goes to stdout
ok 3 - clusterdb --help nothing to stderr
ok 4 - clusterdb --version exit code 0
ok 5 - clusterdb --version goes to stdout
ok 6 - clusterdb --version nothing to stderr
ok 7 - clusterdb with invalid option nonzero exit code
ok 8 - clusterdb with invalid option prints error message
ok 9 - clusterdb exit code 0
not ok 10 - SQL CLUSTER run: SQL found in server log
ok 11 - fails with nonexistent table
ok 12 - clusterdb -t test1 exit code 0
not ok 13 - cluster specific table: SQL found in server log
ok 14 - clusterdb with connection string
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/14 subtests 

#   Failed test 'cluster all databases: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^s:statement: CLUSTER.*statement: CLUSTER)'
# Looks like you failed 1 test of 2.
t/011_clusterdb_all.pl  
1..2
ok 1 - clusterdb -a exit code 0
not ok 2 - cluster all databases: SQL found in server log
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/2 subtests 

#   Failed test 'SQL CREATE DATABASE run: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^:statement: CREATE DATABASE foobar1)'

#   Failed test 'create database with encoding: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^:statement: CREATE DATABASE foobar2 ENCODING 'LATIN1')'
# Looks like you failed 2 tests of 13.
t/020_createdb.pl . 
1..13
ok 1 - createdb --help exit code 0
ok 2 - createdb --help goes to stdout
ok 3 - createdb --help nothing to stderr
ok 4 - createdb --version exit code 0
ok 5 - createdb --version goes to stdout
ok 6 - createdb --version nothing to stderr
ok 7 - createdb with invalid option nonzero exit code
ok 8 - createdb with invalid option prints error message
ok 9 - createdb foobar1 exit code 0
not ok 10 - SQL CREATE DATABASE run: SQL found in server log
ok 11 - createdb -l C -E LATIN1 -T template0 foobar2 exit code 0
not ok 12 - create database with encoding: SQL found in server log
ok 13 - fails if database already exists
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/13 subtests 

#   Failed test 'SQL CREATE EXTENSION run: SQL found in server log'
#   at c:/jenkins/workspace/unicorns/postgresql/src/test/perl/PostgresNode.pm 
line 1321.
#   ''
# doesn't match '(?^:statement: CREATE EXTENSION "plpgsql")'
# Looks like you failed 1 test of 14.




Thanks in advance for any clarification regarding what I might be doing wrong.

Mark Dilger



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

Re: [GENERAL] is (not) distinct from

[Adrian Klaver]

On 03/07/2017 01:22 AM, Johann Spies wrote:

Thanks (again Adrian) and Tom.

The situation was that I had a table with 731million records which I
wanted to copy into a partitioned one where there was a unique
constraint on the fields used in my query.

The "backup" table was the single one.

While inserting into the partitioned table from the backup one, several
(about 12) records failed to insert.

I wanted to find out which records were involved and found that some had
"'" characters in the values which broke some of the functions used to
do some calculations..

As there were fields that might have null values I have tried the "is
not distinct from".

Both sides of the query had primary keys and I did not use group by.
That was why I used "distinct".


Would it not be easier to use a LEFT JOIN between the original 
table(backup) and the new table:


https://www.postgresql.org/docs/9.6/static/sql-select.html

join_type

"LEFT OUTER JOIN returns all rows in the qualified Cartesian product 
(i.e., all combined rows that pass its join condition), plus one copy of 
each row in the left-hand table for which there was no right-hand row 
that passed the join condition. This left-hand row is extended to the 
full width of the joined table by inserting null values for the 
right-hand columns. Note that only the JOIN clause's own condition is 
considered while deciding which rows have matches. Outer conditions are 
applied afterwards."


So something like:

SELECT
a.pk
FROM
original_table AS a
LEFT JOIN--The OUTER is not required
new_table AS b
ON
a.pk = b.pk
WHERE
b.pk IS NULL

That would show all the rows in the original table that where not 
transferred over.




Anyhow in the end, I made some progress with a modified query:

where
s.citing_article = A.citing_article
   and
  s.cited_article !=  A.cited_article
   and
   s.pubyear is  distinct from A.pubyear
   and
   s.year_cited is distinct from A.year_cited
   and
   s.cited_author is distinct from A.cited_author
   and
regexp_replace(s.cited_title,  $$'$$, $$''$$,'g') is distinct from
regexp_replace(A.cited_title,  $$'$$, $$''$$,'g')
   and
regexp_replace(s.cited_work,  $$'$$, $$''$$,'g') is distinct
from regexp_replace(A.cited_work,   $$'$$, $$''$$,'g')
   and
s.doi is distinct from A.doi

Regards.
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Stephen Frost]

Greetings,

* Stephen Frost (sfr...@snowman.net) wrote:
> * Frank van Vugt (ftm.van.v...@foxi.nl) wrote:
> > Well, I didn't run into this issue with any of my db's that 'nicely' use 
> > tables in various schema's, it was actually the one 'older' db with 
> > everything 
> > in the public schema that brought it up, so maybe keeping one of those 
> > around 
> > isn't too bad an idea ;)
> 
> Yeah, I'll be including this in a regression test also, to make sure we
> don't end up breaking this special case again in the future.

I've now pushed a fix for this, would be great to know if it solves your
issue.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Copy database to another host without data from specific tables

[Vick Khera]

On Tue, Mar 7, 2017 at 2:02 AM, Panagiotis Atmatzidis 
wrote:

> I want to make a clone of database1 which belongs to user1, to database2
> which belongs to user2. Database1 has 20+ tables. I want to avoid copying
> the DATA sitting on 5 tables on database1 (many Gigs).
>
> I've read one too many posts about how to perform the actions with
> "pg_dump" and "pg_restore" but I'm trying to figure out the easiest way to
> do this. The process I have in mind is this:
>
>
Your method will break down if you have FKs and you're not careful with the
order you copy your data.

pg_dump has an --exclude-table flag which I would suggest. Just apply it
multiple times to exclude your 5 tables.

Re: [GENERAL] is (not) distinct from

[Johann Spies]

Thanks (again Adrian) and Tom.

The situation was that I had a table with 731million records which I wanted
to copy into a partitioned one where there was a unique constraint on the
fields used in my query.

The "backup" table was the single one.

While inserting into the partitioned table from the backup one, several
(about 12) records failed to insert.

I wanted to find out which records were involved and found that some had
"'" characters in the values which broke some of the functions used to do
some calculations..

As there were fields that might have null values I have tried the "is not
distinct from".

Both sides of the query had primary keys and I did not use group by.  That
was why I used "distinct".

Anyhow in the end, I made some progress with a modified query:

where
s.citing_article = A.citing_article
   and
  s.cited_article !=  A.cited_article
   and
   s.pubyear is  distinct from A.pubyear
   and
   s.year_cited is distinct from A.year_cited
   and
   s.cited_author is distinct from A.cited_author
   and
regexp_replace(s.cited_title,  $$'$$, $$''$$,'g') is distinct from
regexp_replace(A.cited_title,  $$'$$, $$''$$,'g')
   and
regexp_replace(s.cited_work,  $$'$$, $$''$$,'g') is distinct from
regexp_replace(A.cited_work,   $$'$$, $$''$$,'g')
   and
s.doi is distinct from A.doi

Regards.
Johann

-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [GENERAL] Copy database to another host without data from specific tables

[Achilleas Mantzios]

On 07/03/2017 09:02, Panagiotis Atmatzidis wrote:

Hello,

I have 2 RDS instances on AWS running PSQL 9.4.7.

I want to make a clone of database1 which belongs to user1, to database2 which 
belongs to user2. Database1 has 20+ tables. I want to avoid copying the DATA 
sitting on 5 tables on database1 (many Gigs).

I've read one too many posts about how to perform the actions with "pg_dump" and 
"pg_restore" but I'm trying to figure out the easiest way to do this. The process I have 
in mind is this:

1) pg_dump the schema from DB1 to DB2 using --no-owner and pg_restore with 
--role=user2
2) pg_dump -Fc --no-owner --data-only -t 'table1' from DB1 and then restore with 
pg_restore -t 'table' --role=user2  to DB2
So you are talking about DBs on the same PgSQL cluster (RDS instance) or you are trying to clone a DB from 1st RDS instance to the 2nd? In each case, you cannot avoid copying. But if we're talking 
about the whole cluster 

This procedure though is very time consuming (although it could be scripted). 
Is there any better / faster / safer way to do this?

you could design smth based on replication, have a warm/hot standby applying 
changes from the primary, and then implement smth like :
- promote (i.e. recover and start in a new timeline as a new primary)
- run a script to change ownership to user2.
So the idea is to have pre-copied the data, so that the whole final procedure is very fast. Note, however, that after you do that, you would have to re-setup replication again, and that would be 
costly (you can't avoid ultimately copying data). Maybe it could help if you tell us the whole use case.



Thanks.

--
Panagiotis (atmosx) Atmatzidis

email:  a...@convalesco.org
URL:http://www.convalesco.org
GnuPG ID: 0x1A7BFEC5
gpg --keyserver pgp.mit.edu --recv-keys 1A7BFEC5

"Everyone thinks of changing the world, but no one thinks of changing himself.” 
- Leo Tolstoy









--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



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