Re: Static PostgreSQL Binaries (Linux + Windows)

[Pratik Parikh]

Than you. nice to hear from you .  Would be able to share the build scripts
with community? are they open source?

Regards,
Pratik Parikh

On Sat, Jan 12, 2019 at 10:57 PM Zach van Rijn  wrote:

> Hi all,
>
>
> I've been busy porting popular open-source software to various
> platforms (Linux + Windows) via musl- and/or MinGW- based tools,
> as part of a project (and future distro?) called 'xstatic' [1].
>
> All packages are statically linked and have zero dependencies,
> they can be easily reproduced and audited, they are best suited
> for use in environments where software must behave consistently,
> and may have additional performance / safety benefits.
>
> I am pleased to announce the immediate availability of binaries
> (and source code / build scripts) for the following releases:
>
>  release  datelocation
>  ---  --  --
>  latest   n/a https://xstatic.musl.cc/postgresql/
>  11.1 2018-11-08  https://xstatic.musl.cc/postgresql-11.1/
>  10.6 2018-11-08  https://xstatic.musl.cc/postgresql-10.6/
>   9.6.11  2018-11-08  https://xstatic.musl.cc/postgresql-9.6.11/
>   9.5.15  2018-11-08  https://xstatic.musl.cc/postgresql-9.5.15/
>   9.4.20  2018-11-08  https://xstatic.musl.cc/postgresql-9.4.20/
>   9.3.25  2018-11-08  https://xstatic.musl.cc/postgresql-9.3.25/
>
>
> Within each top-level directory, you will find pertaining to an
> architecture/ABI combination such as 'riscv32-linux-musl' (this
> is the target platform where binaries should run), either:
>
> (1) Directory tree (browse / download individual binaries); or
> e.g., https://xstatic.musl.cc/postgresql/riscv32-linux-musl/
>
> (2) Tarball containing the above contents, with a sha512sum that
> is verifiable against '//SHA512SUMS'. Just extract
> and run (or build/link your own software against libraries).
>
>
> PostgreSQL has been built for the following platforms, however,
> not all platforms have been tested (please feel free to help):
>
>   * aarch64-linux-musleabi
>   * aarch64_be-linux-musl
>   * arm-linux-musleabi
>   * arm-linux-musleabihf
>   * armeb-linux-musleabi
>   * armeb-linux-musleabihf
>   * armel-linux-musleabi
>   * armel-linux-musleabihf
>   * armv5l-linux-musleabihf
>   * armv7l-linux-musleabihf
>   * armv7m-linux-musleabi
>   * armv7r-linux-musleabihf
>   * i486-linux-musl
>   * i686-linux-musl
>   * i686-w64-mingw32
>   * m68k-linux-musl
>   * microblaze-linux-musl
>   * microblazeel-linux-musl
>   * mips-linux-musl
>   * mips-linux-musln32sf
>   * mips-linux-muslsf
>   * mips64-linux-musl
>   * mips64-linux-musln32
>   * mips64-linux-musln32sf
>   * mips64el-linux-musl
>   * mips64el-linux-musln32
>   * mips64el-linux-musln32sf
>   * mipsel-linux-musl
>   * mipsel-linux-musln32
>   * mipsel-linux-musln32sf
>   * mipsel-linux-muslsf
>   * or1k-linux-musl
>   * powerpc-linux-musl
>   * powerpc-linux-muslsf
>   * powerpc64-linux-musl
>   * powerpc64le-linux-musl
>   * powerpcle-linux-musl
>   * powerpcle-linux-muslsf
>   * riscv32-linux-musl
>   * riscv64-linux-musl
>   * s390x-linux-musl
>   * sh2-linux-musl
>   * sh2-linux-muslfdpic
>   * sh2eb-linux-musl
>   * sh2eb-linux-muslfdpic
>   * sh4-linux-musl
>   * sh4eb-linux-musl
>   * x86_64-linux-musl
>   * x86_64-linux-muslx32
>   * x86_64-w64-mingw32
>
>
> Quickly testing on Ubuntu 14.04 LTS (GNU/Linux 3.4.98 armv7l):
>
>   $ file ./armv7l-linux-musleabihf/bin/psql
>   psql: ELF 32-bit LSB  executable, ARM, EABI5 version 1 (SYSV),
>   statically linked, stripped
>
>   $ ./armv7l-linux-musleabihf/bin/psql --version
>   psql (PostgreSQL) 11.1
>
>   $ ./armv7l-linux-musleabihf/bin/psql \
> -h pellefant.db.elephantsql.com\
> -U abcdefgh
>   Password:
>   psql (11.1, server 9.5.2)
>   Type "help" for help.
>
>   abcdefgh=>
>
>
> The directory listing looks something like: http://ix.io/1yaV
>
> That said, if you find bugs or encounter issues, please file a
> bug report here [2]. Windows support may need tweaking.
>
>
> Regards,
>
> ZV
>
>
> [1]: https://xstatic.musl.cc/
>
> [2]: https://git.zv.io/xstatic/builder/issues
>
>
>

-- 
Pratik Parikh
- Mantra - Keep It Simple and Straightforward

Static PostgreSQL Binaries (Linux + Windows)

[Zach van Rijn]

Hi all,


I've been busy porting popular open-source software to various
platforms (Linux + Windows) via musl- and/or MinGW- based tools,
as part of a project (and future distro?) called 'xstatic' [1].

All packages are statically linked and have zero dependencies,
they can be easily reproduced and audited, they are best suited
for use in environments where software must behave consistently,
and may have additional performance / safety benefits.

I am pleased to announce the immediate availability of binaries
(and source code / build scripts) for the following releases:

 release  datelocation
 ---  --  --
 latest   n/a https://xstatic.musl.cc/postgresql/
 11.1 2018-11-08  https://xstatic.musl.cc/postgresql-11.1/
 10.6 2018-11-08  https://xstatic.musl.cc/postgresql-10.6/
  9.6.11  2018-11-08  https://xstatic.musl.cc/postgresql-9.6.11/
  9.5.15  2018-11-08  https://xstatic.musl.cc/postgresql-9.5.15/
  9.4.20  2018-11-08  https://xstatic.musl.cc/postgresql-9.4.20/
  9.3.25  2018-11-08  https://xstatic.musl.cc/postgresql-9.3.25/


Within each top-level directory, you will find pertaining to an
architecture/ABI combination such as 'riscv32-linux-musl' (this
is the target platform where binaries should run), either:

(1) Directory tree (browse / download individual binaries); or
e.g., https://xstatic.musl.cc/postgresql/riscv32-linux-musl/

(2) Tarball containing the above contents, with a sha512sum that
is verifiable against '//SHA512SUMS'. Just extract
and run (or build/link your own software against libraries).


PostgreSQL has been built for the following platforms, however,
not all platforms have been tested (please feel free to help):

  * aarch64-linux-musleabi
  * aarch64_be-linux-musl
  * arm-linux-musleabi
  * arm-linux-musleabihf
  * armeb-linux-musleabi
  * armeb-linux-musleabihf
  * armel-linux-musleabi
  * armel-linux-musleabihf
  * armv5l-linux-musleabihf
  * armv7l-linux-musleabihf
  * armv7m-linux-musleabi
  * armv7r-linux-musleabihf
  * i486-linux-musl
  * i686-linux-musl
  * i686-w64-mingw32
  * m68k-linux-musl
  * microblaze-linux-musl
  * microblazeel-linux-musl
  * mips-linux-musl
  * mips-linux-musln32sf
  * mips-linux-muslsf
  * mips64-linux-musl
  * mips64-linux-musln32
  * mips64-linux-musln32sf
  * mips64el-linux-musl
  * mips64el-linux-musln32
  * mips64el-linux-musln32sf
  * mipsel-linux-musl
  * mipsel-linux-musln32
  * mipsel-linux-musln32sf
  * mipsel-linux-muslsf
  * or1k-linux-musl
  * powerpc-linux-musl
  * powerpc-linux-muslsf
  * powerpc64-linux-musl
  * powerpc64le-linux-musl
  * powerpcle-linux-musl
  * powerpcle-linux-muslsf
  * riscv32-linux-musl
  * riscv64-linux-musl
  * s390x-linux-musl
  * sh2-linux-musl
  * sh2-linux-muslfdpic
  * sh2eb-linux-musl
  * sh2eb-linux-muslfdpic
  * sh4-linux-musl
  * sh4eb-linux-musl
  * x86_64-linux-musl
  * x86_64-linux-muslx32
  * x86_64-w64-mingw32


Quickly testing on Ubuntu 14.04 LTS (GNU/Linux 3.4.98 armv7l):

  $ file ./armv7l-linux-musleabihf/bin/psql 
  psql: ELF 32-bit LSB  executable, ARM, EABI5 version 1 (SYSV),
  statically linked, stripped

  $ ./armv7l-linux-musleabihf/bin/psql --version
  psql (PostgreSQL) 11.1

  $ ./armv7l-linux-musleabihf/bin/psql \
-h pellefant.db.elephantsql.com\
-U abcdefgh
  Password:
  psql (11.1, server 9.5.2)
  Type "help" for help.

  abcdefgh=>


The directory listing looks something like: http://ix.io/1yaV

That said, if you find bugs or encounter issues, please file a
bug report here [2]. Windows support may need tweaking.


Regards,

ZV


[1]: https://xstatic.musl.cc/

[2]: https://git.zv.io/xstatic/builder/issues

Re: CREATE COLLATION to match pg_collation data

[Peter Geoghegan]

On Sat, Jan 12, 2019 at 7:13 AM Tom Lane  wrote:
> That's an ICU collation, so you're out of luck: there is no ICU
> support in 9.6.

FWIW, there is some form of FreeBSD Postgres support for ICU that
predates the officially supported ICU feature. The FreeBSD Postgres
packages have been built with ICU support for many years -- they
modify the source code minimally to make this work. It may well still
be impossible to use "az-x-icu" on a FreeBSD installation of 9.6,
though.

-- 
Peter Geoghegan

Re: insert into: NULL in date column

[Rich Shepard]

On Sat, 12 Jan 2019, David G. Johnston wrote:


Inserting new data into a table qualifies as "data change" in my mind...


David,

  Then it's certainly good enough for me. ;-)

  Looking at the text file which the application will replace there are
occasions when there's more than one contact on a single day. So there is no
value in having a date check constraint on this column.

Thanks again,

Rich

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 9:54 AM Rich Shepard  wrote:
> > The problem is that check constraints are only applied at time of data
> > change.

>I thought that the check constraint applied at data entry, too.

Inserting new data into a table qualifies as "data change" in my mind...

David J.

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 10:08 AM Ricardo Martin Gomez
 wrote:
>
> Hi,
> In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember 
> but you can also use the same logic for the check_constraint.
> Regards

Why?  PostgreSQL doesn't need hacks around this...

David J.

Re: insert into: NULL in date column

[Ricardo Martin Gomez]

Hi,
In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember but 
you can also use the same logic for the check_constraint.
Regards

Obtener Outlook para Android


From: Rich Shepard 
Sent: Saturday, January 12, 2019 1:54:47 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: insert into: NULL in date column

On Sat, 12 Jan 2019, David G. Johnston wrote:

> NULL isn't the problem - a check constraint can resolve to unknown in
> which case it behaves the same as if it resolved as true (i.e., its
> basically a  IS NOT FALSE test in the backend). This is actually a
> nice feature of check constraints since for nullable columns you don't
> have to write "col IS NULL OR "

David,

   Thanks for correcting me.

> The problem is that check constraints are only applied at time of data
> change. If you insert a record whose date is 3 days from now the check
> constraint passes today and (in theory) for the next couple of days. After
> which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS
> INSERTED AGAIN. So basically you will not see a problem until you attempt
> to restore your data on some future date and much of your data fails to
> restore because those dates are no longer in the future.

   I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.

> If you want to check for a future date you should probably also store the
> date you are comparing against and have the check constraint reference
> both fields.

   The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.

Best regards,

Rich

Re: insert into: NULL in date column

[Rich Shepard]

On Sat, 12 Jan 2019, David G. Johnston wrote:


NULL isn't the problem - a check constraint can resolve to unknown in
which case it behaves the same as if it resolved as true (i.e., its
basically a  IS NOT FALSE test in the backend). This is actually a
nice feature of check constraints since for nullable columns you don't
have to write "col IS NULL OR "


David,

  Thanks for correcting me.


The problem is that check constraints are only applied at time of data
change. If you insert a record whose date is 3 days from now the check
constraint passes today and (in theory) for the next couple of days. After
which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS
INSERTED AGAIN. So basically you will not see a problem until you attempt
to restore your data on some future date and much of your data fails to
restore because those dates are no longer in the future.


  I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.


If you want to check for a future date you should probably also store the
date you are comparing against and have the check constraint reference
both fields.


  The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.

Best regards,

Rich

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard  wrote:
>
> On Sat, 12 Jan 2019, David G. Johnston wrote:
>
> > Actually, you didn't ask about the check constraint, which is actually
> > horribly broken since current_date is not an immutable function.
>>
>I know that nulls cannot be validly used in comparisons which makes the
> check constraint FUBAR.

NULL isn't the problem - a check constraint can resolve to unknown in
which case it behaves the same as if it resolved as true (i.e., its
basically a  IS NOT FALSE test in the backend).  This is
actually a nice feature of check constraints since for nullable
columns you don't have to write "col IS NULL OR "

The problem is that check constraints are only applied at time of data
change.  If you insert a record whose date is 3 days from now the
check constraint passes today and (in theory) for the next couple of
days.  After which the constraint fails - but you are INFORMED ONLY IF
THE RECORD IS INSERTED AGAIN.  So basically you will not see a problem
until you attempt to restore your data on some future date and much of
your data fails to restore because those dates are no longer in the
future.

If you want to check for a future date you should probably also store
the date you are comparing against and have the check constraint
reference both fields.

David J.

Re: insert into: NULL in date column

[Rich Shepard]

On Sat, 12 Jan 2019, David G. Johnston wrote:


Actually, you didn't ask about the check constraint, which is actually
horribly broken since current_date is not an immutable function.


David,

  I know that nulls cannot be validly used in comparisons which makes the
check constraint FUBAR.

Thanks,

Rich

Re: insert into: NULL in date column

[David G. Johnston]

On Sat, Jan 12, 2019 at 6:43 AM Rich Shepard  wrote:
>
> On Sat, 12 Jan 2019, Ricardo Martin Gomez wrote:
>
> > Hi, one question.
> > Do you put explicit "NULL" in the column value?
> > Other option is.
> > You have to delete the column "next_contact" in your INSERT clause.
> > So, if the column has a default value, this value Will be inserted. Else
> > Null value will be inserted.
>
> Ricardo,
>
>I thought of using an explicit null and David confirmed that to be the
> solution. Also, he answered my question that having a default and check
> constraint are not needed.

Actually, you didn't ask about the check constraint, which is actually
horribly broken since current_date is not an immutable function.

David J.

Re: CREATE COLLATION to match pg_collation data

[Tom Lane]

rihad  writes:
> Hi, since pg_import_system_collations() that would solve all this 
> doesn't exist in 9.6.x, is there a way to I properly map it to a CREATE 
> COLLATE call? Specifically I need to have this collation (from 10.x) in 
> 9.6.x so it works on FreeBSD:

>      collname    | collnamespace | collowner | collprovider | 
> collencoding |    collcollate    | collctype | collversion
> +---+---+--+--+---+---+-
>   az-x-icu   |    11 |    10 | i    
> |   -1 | az    | az    | 153.88.34

That's an ICU collation, so you're out of luck: there is no ICU
support in 9.6.

regards, tom lane

Re: insert into: NULL in date column

[Rich Shepard]

On Sat, 12 Jan 2019, Ricardo Martin Gomez wrote:


Hi, one question.
Do you put explicit "NULL" in the column value?
Other option is.
You have to delete the column "next_contact" in your INSERT clause.
So, if the column has a default value, this value Will be inserted. Else
Null value will be inserted.


Ricardo,

  I thought of using an explicit null and David confirmed that to be the
solution. Also, he answered my question that having a default and check
constraint are not needed.

Thanks,

Rich

Re: insert into: NULL in date column

[Rich Shepard]

On Fri, 11 Jan 2019, David G. Johnston wrote:


The default does seem a bit arbitrary and pointless...


David,

  That answers my question about it. Thanks again.

Best regards,

Rich

Re: insert into: NULL in date column

[Rich Shepard]

On Fri, 11 Jan 2019, David G. Johnston wrote:


VALUES (1, null, 3) is valid, VALUES (1,,3) is not.


David,

  Using null occurred to me when I saw that an empty space still failed.
Thanks for clarifying and confirming.

Best regards,

Rich