Re: [GENERAL] My first PL/pgSQL function

[Pavel Stehule]

2015-10-21 4:08 GMT+02:00 Dane Foster :

> Since I'm switching to OUT parameters is there any difference
> (performance/efficiency wise) between using an INTO STRICT
> RECORD_TYPE_VARIABLE statement which forces me to copy/assign the property
> values from the RECORD to the OUT parameter variables and simply listing
> the OUT parameters, i.e., INTO STRICT outparam1, outparam2, ..., outparamN?
>

It strongly depends on what do you do. I artificial benchmarks you can find
tens percent difference (based on massive cycles), but in life there will
be zero difference probably. The bottleneck in PLpgSQL functions are SQL
statements usually, and the overhead of "glue" is pretty less. Mainly if
you has not any loop there.

Regards

Pavel



>
> Thanks,
>
> Dane
>
> On Tue, Oct 20, 2015 at 4:37 PM, Pavel Stehule 
> wrote:
>
>>
>>
>> 2015-10-20 22:22 GMT+02:00 Dane Foster :
>>
>>> Here is the updated version w/ the feedback incorporated. I'm going to
>>> install PostgreSQL 9.6 from source this weekend so I can start
>>> testing/debugging. Does anyone here have any experience using the pgAdmin
>>> debugger recently? I ask because it seems a little dated (September 26,
>>> 2008).
>>>
>>>
>>> Thanks,
>>>
>>> Dane
>>>
>>> /**
>>>  * Returns the status of a coupon or voucher.
>>>  * @param _code The discount code.
>>>  * @return NULL if the discount does not exist otherwise a composite
>>> type (see return
>>>  * type declaration below).
>>>
>>>  *
>>>  * Voucher codes have the following properties:
>>>  * type - The type of discount (voucher, giftcert).
>>>  *
>>>  * status   - The status of the voucher. The valid values are:
>>>  *void - The voucher has been voided.
>>>  *
>>>  *expired  - The voucher has expired.
>>>  *
>>>  *inactive - The gift certificate has not been sent yet.
>>>  *
>>>  *ok   - The voucher has been activated, has not
>>> expired, and has a
>>>  *   current value greater than zero.
>>>  *
>>>  * date - The expiration or activation or void date of the voucher
>>> in a reader
>>>  *friendly format.
>>>  *
>>>  * datetime - The expiration or activation or void date of the gift
>>> certificate in
>>>  *-MM-DD HH:MM:SS format.
>>>  *
>>>  * value- The current value of the voucher.
>>>  *
>>>  * The mandatory properties are type and status. The presence of the
>>> other properties
>>>  * are dependent on the value of status.
>>>
>>>  
>>> 
>>>  * Coupon codes can provide the following additional parameters that are
>>> used to
>>>  * determine if an order meets a coupon's minimum requirements.
>>>  * @param int seats The number of seats in the user's order.
>>>
>>>  * @param numeric subtotal The order's subtotal.
>>>  *
>>>  * Coupon codes have the following properties:
>>>  * type - The type of discount (coupon).
>>>  *
>>>  * status   - The status of the coupon code. The valid values are:
>>>  *void - The coupon has been voided.
>>>  *
>>>  *expired  - The coupon has expired.
>>>  *
>>>  *inactive - The coupon has not been activated yet.
>>>  *
>>>  *min  - The minimum seats or dollar amount requirement
>>> has not been
>>>  *   met.
>>>  *
>>>  *ok   - The coupon can be used.
>>>  *
>>>  * min  - The minimum seats or dollar amount requirement. The value
>>> of this
>>>  *property is either an unsigned integer or dollar amount
>>> string w/ the
>>>  *dollar sign.
>>>  *
>>>  * date - The expiration or activation or void date of the coupon in
>>> a reader
>>>  *friendly format.
>>>  *
>>>  * datetime - The expiration or activation or void date of the coupon in
>>> -MM-DD
>>>  * HH:MM:SS format.
>>>  *
>>>  * value- The current value of the coupon as a string. The value of
>>> this property
>>>  *is either an unsigned integer w/ a percent symbol or
>>> dollar amount
>>>  *string w/ the dollar sign.
>>>  */
>>> CREATE OR REPLACE FUNCTION check_discount_code(
>>>   _code public.CITXT70,
>>>   VARIADIC cpnxtra NUMERIC[]
>>> )
>>> RETURNS TABLE (
>>>   type TEXT,
>>>   status   TEXT,
>>>   date TEXT,
>>>   datetime TIMESTAMPTZ,
>>>   valueTEXT,
>>>   min  TEXT
>>> ) AS $$
>>>
>>
>> it is wrong, you are return composite, not SETOF composites (table).
>>
>> Use OUT parameters instead or declared custom type
>>
>> CREATE TYPE foo_result_type AS (a int, b int, c int);
>> CREATE OR REPLACE FUNCTION foo(..) RETURNS foo_result_type AS $$ $$
>>
>>
>>
>>> DECLARE
>>>   discount RECORD;
>>> BEGIN
>>>
>>>   SELECT
>>> ok,
>>> created,
>>> expires,
>>> modified,
>>> effective_date,
>>> -- The minimum quantity or dollar amount required to use the coupon.
>>> COALESCE(
>>>   lower(qty_range),
>>>  

Re: [GENERAL] [ADMIN] $libdir/mysql_fdw

[Devrim GÜNDÜZ]

Hi,

On Thu, 2015-08-27 at 12:46 -0500, Ryan King - NOAA Affiliate wrote:
> ERROR:  could not access file "$libdir/mysql_fdw": No such file or
> directory
> 
> dbname=# CREATE EXTENSION mysql_fdw;
> 
> ERROR:  could not open extension control file
> "/usr/pgsql-9.4/share/extension/mysql_fdw.control": No such file or
> directory
> 
> What needs to be installed for this to work?

Looks like you installed PostgreSQL using the community RPMs -- so make
sure that you used the RPM of mysql_fdw:

yum install mysql_fdw_94

would do the trick.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ 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] BDR - DDL Locking

[Craig Ringer]

What's the *exact* BDR version?

When you say you "attempted to" - what was the outcome? Presumably an
ERROR from the TRUNCATE, right? That would roll back the transaction,
and in the process abort the DDL lock acquisition attempt.

Are you sure replication was working normally prior to this point,
with no issues?

The global DDL lock isn't a true lock in the sense that it appears in
pg_locks, etc. If you roll back the transaction trying to acquire it,
or terminate the PostgreSQL backend attempting to acquire it - such as
your TRUNCATE - using pg_terminate_backend(...) then it will be
removed automatically. If for any reason that is not the case (which
it shouldn't be) then restarting the nodes will clear 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] trouble downloading postgres 9.4 for RHEL 6.x

[Adrian Klaver]

On 10/20/2015 05:48 PM, anj patnaik wrote:

Several weeks ago, I successfully
downloaded postgresql-9.4.4-3-linux-x64.run on a Linux server.


Where did you download from?


Today, I attempted to download from the site. I notice 9.5 is there, but
getting lots of errors:


Looks like you are trying to run a *.exe(Windows) file on a Linux 
machine, that is the source of the errors.




1) when downloading and running latest

rchive:  /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
   End-of-central-directory signature not found.  Either this file is not
   a zipfile, or it constitutes one disk of a multi-part archive.  In the
   latter case the central directory and zipfile comment will be found on
   the last disk(s) of this archive.
zipinfo:  cannot find zipfile directory in one of
/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or

/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip, and
cannot find
/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP, period.

2) Then I tried to scp from the other machine and changed permissions to
777 and used sudo to execute the file but get this:

bash-4.1$ chmod 777 postgresql-9.4.4-3-linux-x64.run
bash-4.1$ sudo ./postgresql-9.4.4-3-linux-x64.run
Installer payload initialization failed. This is likely due to an
incomplete or corrupt downloaded file.


What is the recommended 9.4 version to download from and the steps?


What OS and version?



I want to use the graphical installer since I used it last time.


Assuming you mean the EDB installer:

http://www.enterprisedb.com/products-services-training/pgdownload



Thanks a lot!!



--
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] BDR - DDL Locking

[Will McCormick]

Hey Craig thank you very much for your response.

> When you say you "attempted to" - what was the outcome?

I tried a truncate without the cascade option. After that I tried it with
the cascade option. The session just hanged indefinitely at that point.
There was no rollback and I was testing on an empty table.

Replication was in a ready state on both nodes and both DDL and DML was
replicating.

0.9.2.0 BDR

When you say restarting the nodes. I did restart postgres and this didn't
help.


On Wed, Oct 21, 2015 at 8:31 AM, Craig Ringer  wrote:

> What's the *exact* BDR version?
>
> When you say you "attempted to" - what was the outcome? Presumably an
> ERROR from the TRUNCATE, right? That would roll back the transaction,
> and in the process abort the DDL lock acquisition attempt.
>
> Are you sure replication was working normally prior to this point,
> with no issues?
>
> The global DDL lock isn't a true lock in the sense that it appears in
> pg_locks, etc. If you roll back the transaction trying to acquire it,
> or terminate the PostgreSQL backend attempting to acquire it - such as
> your TRUNCATE - using pg_terminate_backend(...) then it will be
> removed automatically. If for any reason that is not the case (which
> it shouldn't be) then restarting the nodes will clear it.
>

Re: [GENERAL] BDR - DDL Locking

[Craig Ringer]

Will,

I saw after replying that there's more detail I missed in your mail,
so please see the more detailed reply inline below.

On 20 October 2015 at 23:31, Will McCormick  wrote:
> First time user here and new to PostgreSQL and BDR so I hope I have the
> right place.

You do.

> I attempted to issues a TRUNCATE TABLE without the cascade option on a
> Parent table that had a child FK constraint.

I've looked at your logs, and it looks like the TRUNCATE suceeded on
the node that was doing the DDL and it was queued for replication.
Then, when applying to another node, it failed because there was a
foreign key relationship referencing the target table.

This is odd, because the way BDR captures TRUNCATEs should prevent
that from happening. It uses triggers to capture TRUNCATES and
enqueues them for execution. However, I can see upon inspection that
the approach used just isn't sufficient to handle FK relationships,
and that the current test suite doesn't cover this.

I'm going to write a test to confirm what I think is going on, then follow up.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Multiple word synonyms (maybe?)

[Kevin Grittner]

On Tuesday, October 20, 2015 7:56 PM, Tim van der Linden  wrote:
> On Tue, 20 Oct 2015 12:02:46 +0100

>> ​Does the Thesaurus dictionary not do what you want?​
>> ​
>> http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-THESAURUS
>
> Damn, I completely overlooked that one, and it indeed does seem
> to come very close to what I need in this use case.

I have to admit that the name of that dictionary type threw me off
a bit at first.

> If I am not mistaken, this would be a valid thesaurus file:
>
> acute mi : heart attack
> mi : heart attack
> myocardial infarction : heart attack​
>
> Multiple words on both ends, separated by a colon and each line
> being functional (a unique phrase linked to its more generic
> replacement)?

It has been a while, but my recollection is that I did something
more like this:

heart attack : heartattack
acute mi : heartattack
mi : heartattack
myocardial infarction : heartattack​

If my memory is to be trusted, both the original words (whichever
are actually in the document) and the "invented" synonym
("heartattack") will be in the tsvector/tsquery; this results in
all *matching* but the identical wording being considered a *closer
match*.

As with most things, I encourage you to play around with it a bit
to see what gives the best results for you.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] My first PL/pgSQL function

[Dane Foster]

On Wed, Oct 21, 2015 at 3:20 AM, Pavel Stehule 
wrote:

>
>
> 2015-10-21 4:08 GMT+02:00 Dane Foster :
>
>> Since I'm switching to OUT parameters is there any difference
>> (performance/efficiency wise) between using an INTO STRICT
>> RECORD_TYPE_VARIABLE statement which forces me to copy/assign the property
>> values from the RECORD to the OUT parameter variables and simply listing
>> the OUT parameters, i.e., INTO STRICT outparam1, outparam2, ..., outparamN?
>>
>
> It strongly depends on what do you do. I artificial benchmarks you can
> find tens percent difference (based on massive cycles), but in life there
> will be zero difference probably. The bottleneck in PLpgSQL functions are
> SQL statements usually, and the overhead of "glue" is pretty less. Mainly
> if you has not any loop there.
>
> Regards
>
> Pavel
>
>
>
>>
>> Thanks,
>>
>> Dane
>>
>> On Tue, Oct 20, 2015 at 4:37 PM, Pavel Stehule 
>> wrote:
>>
>>>
>>>
>>> 2015-10-20 22:22 GMT+02:00 Dane Foster :
>>>
 Here is the updated version w/ the feedback incorporated. I'm going to
 install PostgreSQL 9.6 from source this weekend so I can start
 testing/debugging. Does anyone here have any experience using the pgAdmin
 debugger recently? I ask because it seems a little dated (September 26,
 2008).


 Thanks,

 Dane

 /**
  * Returns the status of a coupon or voucher.
  * @param _code The discount code.
  * @return NULL if the discount does not exist otherwise a composite
 type (see return
  * type declaration below).

  *
  * Voucher codes have the following properties:
  * type - The type of discount (voucher, giftcert).
  *
  * status   - The status of the voucher. The valid values are:
  *void - The voucher has been voided.
  *
  *expired  - The voucher has expired.
  *
  *inactive - The gift certificate has not been sent yet.
  *
  *ok   - The voucher has been activated, has not
 expired, and has a
  *   current value greater than zero.
  *
  * date - The expiration or activation or void date of the voucher
 in a reader
  *friendly format.
  *
  * datetime - The expiration or activation or void date of the gift
 certificate in
  *-MM-DD HH:MM:SS format.
  *
  * value- The current value of the voucher.
  *
  * The mandatory properties are type and status. The presence of the
 other properties
  * are dependent on the value of status.

  
 
  * Coupon codes can provide the following additional parameters that
 are used to
  * determine if an order meets a coupon's minimum requirements.
  * @param int seats The number of seats in the user's order.

  * @param numeric subtotal The order's subtotal.
  *
  * Coupon codes have the following properties:
  * type - The type of discount (coupon).
  *
  * status   - The status of the coupon code. The valid values are:
  *void - The coupon has been voided.
  *
  *expired  - The coupon has expired.
  *
  *inactive - The coupon has not been activated yet.
  *
  *min  - The minimum seats or dollar amount requirement
 has not been
  *   met.
  *
  *ok   - The coupon can be used.
  *
  * min  - The minimum seats or dollar amount requirement. The value
 of this
  *property is either an unsigned integer or dollar amount
 string w/ the
  *dollar sign.
  *
  * date - The expiration or activation or void date of the coupon
 in a reader
  *friendly format.
  *
  * datetime - The expiration or activation or void date of the coupon
 in -MM-DD
  * HH:MM:SS format.
  *
  * value- The current value of the coupon as a string. The value of
 this property
  *is either an unsigned integer w/ a percent symbol or
 dollar amount
  *string w/ the dollar sign.
  */
 CREATE OR REPLACE FUNCTION check_discount_code(
   _code public.CITXT70,
   VARIADIC cpnxtra NUMERIC[]
 )
 RETURNS TABLE (
   type TEXT,
   status   TEXT,
   date TEXT,
   datetime TIMESTAMPTZ,
   valueTEXT,
   min  TEXT
 ) AS $$

>>>
>>> it is wrong, you are return composite, not SETOF composites (table).
>>>
>>> Use OUT parameters instead or declared custom type
>>>
>>> CREATE TYPE foo_result_type AS (a int, b int, c int);
>>> CREATE OR REPLACE FUNCTION foo(..) RETURNS foo_result_type AS $$ $$
>>>
>>>
>>>
 DECLARE
   discount RECORD;
 BEGIN

   SE

Re: [GENERAL] My first PL/pgSQL function

[Pavel Stehule]

> ​For posterity here is the final version. I ran it through PostgreSQL
> 9.5beta1 this morning so it's at least syntactically valid. Additionally I
> went w/ a list of INTO targets instead of a RECORD because it's a more
> elegant solution in that it made the code a little less verbose and a
> little less repetitive. The fact that in some cases it's faster is a
> serendipitous bonus.
>
> Though the conversation around this function has improved my understanding
> of PL/pgSQL immensely there are a couple things that happened that I don't
> fully understand:
>
> 1. I've changed the function's argument list from: (text, variadic
> numeric[]) to: (text, int default, numeric default) because I couldn't
> get the variadic version to work when only one argument was passed to the
> function. For example:
> SELECT * FROM check_discount_code('blah')
> caused PostreSQL to complained that "no function w/ that signature exists
> you may need to cast" (I'm paraphrasing). In order to get it to work I had
> to provide at least two arguments.
>

yes - the variadic *functions* can have parameters with default values or
*variadic parameters* or mix. It is similar to Visual Basic for example.
The specific PostgreSQL feature is support function overloading and
variadic functions together. When you call any function, then PostgreSQL
have to choose function with the closest param types - and usually Postgres
try to cast parameters to different type less times than other databases.


>
> 2. I was under the impression that the runtime environment of PL/pgSQL is
> the same environment PostgreSQL uses to execute all SQL commands and
> functions. So if that's true why is returning JSON from inside a PL/pgSQL
> function so much more expensive than doing it outside?
>

JSON in Postgres is string. If you need a access to any field, then this
string have to be parsed and the data must be deserialized. The same behave
has XML format. If I don't plan to process result of function on server
side, then returning XML or JSON is ok. But If I'll process result in other
server side functions, then native tuples are better (if nested structures
are not needed.)

Regards

Pavel


>
> Dane
> ​
> ​
>
>

Re: [GENERAL] My first PL/pgSQL function

[Merlin Moncure]

On Wed, Oct 21, 2015 at 10:24 AM, Dane Foster  wrote:
> For posterity here is the final version. I ran it through PostgreSQL
> 9.5beta1 this morning so it's at least syntactically valid. Additionally I
> went w/ a list of INTO targets instead of a RECORD because it's a more
> elegant solution in that it made the code a little less verbose and a little
> less repetitive. The fact that in some cases it's faster is a serendipitous
> bonus.

*) I really dislike your formatting relative to what it was.  In fact,
it's a mess. You went to four spaces vs two, which causes a lot of
issues IMO.  SQL relative to other languages requires a lot of nested
indentation and this makes things challenging to format well.   This
is opinion I guess but I think it's a regression.

aside: do not use tabs in sql, ever -- I dislike tabs generally but in
postgres .sql it breaks pasting to psql.

*) your indent spacing is not regular (meaning, not always exactly 4).
For example,

END CASE;
  END IF;

Irregular indentation leads directly to:
*) lots of extra work and irrelevant-to-the-actual-change whitespace adjustments
*) coding difficulty locating the end of the block in the face of
indentation changes.  At the bottom portion of your function, I'm not
easily finding the matching END IF to the:

ELSE
-- This should NEVER happen!
IF danglingvoucher

merlin


-- 
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] trouble downloading postgres 9.4 for RHEL 6.x

[Adrian Klaver]

On 10/21/2015 08:57 AM, anj patnaik wrote:

I used the same link:
http://www.enterprisedb.com/products-services-training/pgdownload

I chose /*Version 9.5.0 Beta 1*/ Linux x86-64


I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran it. I did not 
actually complete the install as I already have Postgres installed  on 
this machine. This is on openSUSE 13.2, not that it should matter.




Then, I tried 9.4 for Linux x86-64

Has anyone downloaded/installed within last 2 days? my OS is RHEL 6.5

I am using the Linux machine's firefox browser to download.

is there a way to use yum to get the same installer program that goes
through all the steps?


No, Yum will not use the installer program, it will use the RH native 
packaging.


Is there a particular reason you want the installer?

Something specific you want to install?



Please advise. thanks

On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/20/2015 05:48 PM, anj patnaik wrote:

Several weeks ago, I successfully
downloaded postgresql-9.4.4-3-linux-x64.run on a Linux server.


Where did you download from?


Today, I attempted to download from the site. I notice 9.5 is
there, but
getting lots of errors:


Looks like you are trying to run a *.exe(Windows) file on a Linux
machine, that is the source of the errors.


1) when downloading and running latest

rchive:  /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
End-of-central-directory signature not found.  Either this
file is not
a zipfile, or it constitutes one disk of a multi-part
archive.  In the
latter case the central directory and zipfile comment will
be found on
the last disk(s) of this archive.
zipinfo:  cannot find zipfile directory in one of
/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or

/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip, and
cannot find
/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP,
period.

2) Then I tried to scp from the other machine and changed
permissions to
777 and used sudo to execute the file but get this:

bash-4.1$ chmod 777 postgresql-9.4.4-3-linux-x64.run
bash-4.1$ sudo ./postgresql-9.4.4-3-linux-x64.run
Installer payload initialization failed. This is likely due to an
incomplete or corrupt downloaded file.


What is the recommended 9.4 version to download from and the steps?


What OS and version?


I want to use the graphical installer since I used it last time.


Assuming you mean the EDB installer:

http://www.enterprisedb.com/products-services-training/pgdownload


Thanks a lot!!



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





--
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

[GENERAL] Configure with Openssl fails

[Michael Hartung]

Hi,

being on OS X 10.11 (El Capitain) and trying a VPATH build with plain 
"configure" and "make" works fine.


/Users/me/Documents/workspace/postgres/configure --with-openssl 
--with-includes=/usr/local/ssl/include/openssl 
--with-libraries=/usr/local/ssl/lib


fails and leads to:
...
checking openssl/ssl.h usability... no
checking openssl/ssl.h presence... no
checking for openssl/ssl.h... no
configure: error: header file  is required for OpenSSL

Any ideas...?

Thanks in advance!
Michael






--
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] Configure with Openssl fails

[Adrian Klaver]

On 10/21/2015 09:16 AM, Michael Hartung wrote:

Hi,

being on OS X 10.11 (El Capitain) and trying a VPATH build with plain
"configure" and "make" works fine.

/Users/me/Documents/workspace/postgres/configure --with-openssl
--with-includes=/usr/local/ssl/include/openssl
--with-libraries=/usr/local/ssl/lib

fails and leads to:
...
checking openssl/ssl.h usability... no
checking openssl/ssl.h presence... no
checking for openssl/ssl.h... no
configure: error: header file  is required for OpenSSL

Any ideas...?


I would say you need the OpenSSL development libraries.


Thanks in advance!
Michael









--
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] trouble downloading postgres 9.4 for RHEL 6.x

[Adrian Klaver]

On 10/21/2015 09:14 AM, anj patnaik wrote:

Ok, i am trying to determine why I am getting errors. Is it possible
that my browser is corrupting the transfer?


Maybe, though I used FireFox to download also. I would go to wherever 
the file has been downloaded on your computer and delete it and try the 
download again. The file I got was:


37548416 Oct 21 09:20 postgresql-9.5.0-beta1-linux-x64.run

CCing list


I am seeing: Installer payload initialization failed. This is likely due
to an incomplete or corrupt downloaded file.

I am a Linux newbie. Is there a way to test if the transfers are getting
corrupted?

after download, did you just change permissions and ran sudo
./postgres.run file?


Yes, so:

aklaver@killi:~/Downloads> chmod 755 postgresql-9.5.0-beta1-linux-x64.run

aklaver@killi:~/Downloads> sudo ./postgresql-9.5.0-beta1-linux-x64.run



The reason for using installer is that it goes through all the steps and
instantiates a sid.


What is a sid?



The purpose of this installation is for me to have a database to rest
the restoration of pg_dump.


You can also get that with a Yum install, see below for more:

http://www.postgresql.org/download/linux/redhat/


Thank you.

On Wed, Oct 21, 2015 at 12:07 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/21/2015 08:57 AM, anj patnaik wrote:

I used the same link:
http://www.enterprisedb.com/products-services-training/pgdownload

I chose /*Version 9.5.0 Beta 1*/ Linux x86-64


I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran it. I did
not actually complete the install as I already have Postgres
installed  on this machine. This is on openSUSE 13.2, not that it
should matter.


Then, I tried 9.4 for Linux x86-64

Has anyone downloaded/installed within last 2 days? my OS is
RHEL 6.5

I am using the Linux machine's firefox browser to download.

is there a way to use yum to get the same installer program that
goes
through all the steps?


No, Yum will not use the installer program, it will use the RH
native packaging.

Is there a particular reason you want the installer?

Something specific you want to install?


Please advise. thanks

On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:

 On 10/20/2015 05:48 PM, anj patnaik wrote:

 Several weeks ago, I successfully
 downloaded postgresql-9.4.4-3-linux-x64.run on a Linux
server.


 Where did you download from?


 Today, I attempted to download from the site. I notice
9.5 is
 there, but
 getting lots of errors:


 Looks like you are trying to run a *.exe(Windows) file on a
Linux
 machine, that is the source of the errors.


 1) when downloading and running latest

 rchive:
/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe

[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
 End-of-central-directory signature not found.
Either this
 file is not
 a zipfile, or it constitutes one disk of a multi-part
 archive.  In the
 latter case the central directory and zipfile
comment will
 be found on
 the last disk(s) of this archive.
 zipinfo:  cannot find zipfile directory in one of

/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or


/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip, and
 cannot find

/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP,
 period.

 2) Then I tried to scp from the other machine and changed
 permissions to
 777 and used sudo to execute the file but get this:

 bash-4.1$ chmod 777 postgresql-9.4.4-3-linux-x64.run
 bash-4.1$ sudo ./postgresql-9.4.4-3-linux-x64.run
 Installer payload initialization failed. This is likely
due to an
 incomplete or corrupt downloaded file.


 What is the recommended 9.4 version to download from
and the steps?


 What OS and version?


 I want to use the graphical installer since I used it
last time.


 Assuming you mean the EDB installer:

http://www.enterprisedb.com/products-services-training/pgdownload


 Thanks a lot!!



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

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

[anj patnaik]

I used the same link:
http://www.enterprisedb.com/products-services-training/pgdownload

I chose *Version 9.5.0 Beta 1* Linux x86-64

Then, I tried 9.4 for Linux x86-64

Has anyone downloaded/installed within last 2 days? my OS is RHEL 6.5

I am using the Linux machine's firefox browser to download.

is there a way to use yum to get the same installer program that goes
through all the steps?

Please advise. thanks

On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver 
wrote:

> On 10/20/2015 05:48 PM, anj patnaik wrote:
>
>> Several weeks ago, I successfully
>> downloaded postgresql-9.4.4-3-linux-x64.run on a Linux server.
>>
>
> Where did you download from?
>
>>
>> Today, I attempted to download from the site. I notice 9.5 is there, but
>> getting lots of errors:
>>
>
> Looks like you are trying to run a *.exe(Windows) file on a Linux machine,
> that is the source of the errors.
>
>
>> 1) when downloading and running latest
>>
>> rchive:  /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
>> [/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
>>End-of-central-directory signature not found.  Either this file is not
>>a zipfile, or it constitutes one disk of a multi-part archive.  In the
>>latter case the central directory and zipfile comment will be found on
>>the last disk(s) of this archive.
>> zipinfo:  cannot find zipfile directory in one of
>> /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or
>>
>> /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip, and
>> cannot find
>> /home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP, period.
>>
>> 2) Then I tried to scp from the other machine and changed permissions to
>> 777 and used sudo to execute the file but get this:
>>
>> bash-4.1$ chmod 777 postgresql-9.4.4-3-linux-x64.run
>> bash-4.1$ sudo ./postgresql-9.4.4-3-linux-x64.run
>> Installer payload initialization failed. This is likely due to an
>> incomplete or corrupt downloaded file.
>>
>>
>> What is the recommended 9.4 version to download from and the steps?
>>
>
> What OS and version?
>
>
>> I want to use the graphical installer since I used it last time.
>>
>
> Assuming you mean the EDB installer:
>
> http://www.enterprisedb.com/products-services-training/pgdownload
>
>
>> Thanks a lot!!
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] Configure with Openssl fails

[Michael Hartung]

Hi,

being on OS X 10.11 (El Capitain) and trying a VPATH build with plain 
"configure" and "make" works fine.


/Users/me/Documents/workspace/postgres/configure --with-openssl 
--with-includes=/usr/local/ssl/include/openssl 
--with-libraries=/usr/local/ssl/lib


fails and leads to:

...
checking openssl/ssl.h usability... no
checking openssl/ssl.h presence... no
checking for openssl/ssl.h... no
configure: error: header file  is required for OpenSSL

Any ideas...?

Thanks in advance!
Michael











--
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] trouble downloading postgres 9.4 for RHEL 6.x

[Scott Mead]

> On Oct 21, 2015, at 12:28, Adrian Klaver  wrote:
> 
>> On 10/21/2015 09:14 AM, anj patnaik wrote:
>> Ok, i am trying to determine why I am getting errors. Is it possible
>> that my browser is corrupting the transfer?
> 
> Maybe, though I used FireFox to download also. I would go to wherever the 
> file has been downloaded on your computer and delete it and try the download 
> again. The file I got was:
> 
> 37548416 Oct 21 09:20 postgresql-9.5.0-beta1-linux-x64.run
> 
> CCing list
>> 
>> I am seeing: Installer payload initialization failed. This is likely due
>> to an incomplete or corrupt downloaded file.
>> 
>> I am a Linux newbie. Is there a way to test if the transfers are getting
>> corrupted?
>> 
>> after download, did you just change permissions and ran sudo
>> ./postgres.run file?
> 
> Yes, so:
> 
> aklaver@killi:~/Downloads> chmod 755 postgresql-9.5.0-beta1-linux-x64.run
> 
> aklaver@killi:~/Downloads> sudo ./postgresql-9.5.0-beta1-linux-x64.run
> 
>> 
>> The reason for using installer is that it goes through all the steps and
>> instantiates a sid.
> 
> What is a sid?
> 

Postgres isn't like Oracle. Use yum to install, then run 

/etc/init.d/postgres9.5 initdb

Then, run:
/etc/init.d/postgres9.5 start


  That's it.  Initdb creates the instance, start - starts it.  No dbca or other 
such garbage is needed. Welcome to the easy life :)



>> 
>> The purpose of this installation is for me to have a database to rest
>> the restoration of pg_dump.
> 
> You can also get that with a Yum install, see below for more:
> 
> http://www.postgresql.org/download/linux/redhat/
>> 
>> Thank you.
>> 
>> On Wed, Oct 21, 2015 at 12:07 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>> 
>>On 10/21/2015 08:57 AM, anj patnaik wrote:
>> 
>>I used the same link:
>>http://www.enterprisedb.com/products-services-training/pgdownload
>> 
>>I chose /*Version 9.5.0 Beta 1*/ Linux x86-64
>> 
>> 
>>I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran it. I did
>>not actually complete the install as I already have Postgres
>>installed  on this machine. This is on openSUSE 13.2, not that it
>>should matter.
>> 
>> 
>>Then, I tried 9.4 for Linux x86-64
>> 
>>Has anyone downloaded/installed within last 2 days? my OS is
>>RHEL 6.5
>> 
>>I am using the Linux machine's firefox browser to download.
>> 
>>is there a way to use yum to get the same installer program that
>>goes
>>through all the steps?
>> 
>> 
>>No, Yum will not use the installer program, it will use the RH
>>native packaging.
>> 
>>Is there a particular reason you want the installer?
>> 
>>Something specific you want to install?
>> 
>> 
>>Please advise. thanks
>> 
>>On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
>>mailto:adrian.kla...@aklaver.com>
>>>>> wrote:
>> 
>> On 10/20/2015 05:48 PM, anj patnaik wrote:
>> 
>> Several weeks ago, I successfully
>> downloaded postgresql-9.4.4-3-linux-x64.run on a Linux
>>server.
>> 
>> 
>> Where did you download from?
>> 
>> 
>> Today, I attempted to download from the site. I notice
>>9.5 is
>> there, but
>> getting lots of errors:
>> 
>> 
>> Looks like you are trying to run a *.exe(Windows) file on a
>>Linux
>> machine, that is the source of the errors.
>> 
>> 
>> 1) when downloading and running latest
>> 
>> rchive:
>>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
>> 
>>[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
>> End-of-central-directory signature not found.
>>Either this
>> file is not
>> a zipfile, or it constitutes one disk of a multi-part
>> archive.  In the
>> latter case the central directory and zipfile
>>comment will
>> be found on
>> the last disk(s) of this archive.
>> zipinfo:  cannot find zipfile directory in one of
>> 
>>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or
>> 
>> 
>>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip, and
>> cannot find
>> 
>>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP,
>> period.
>> 
>> 2) Then I tried to scp from the other machine and changed
>> permissions to
>> 777 and used sudo to execute the file but get this:
>> 
>> bash-4.1$ chmod 777 postgresql-9.4.4-3-linux-x64.run
>> bash-4.1$ sudo ./postgresql-9.4.4-3-linux-x64.run
>> Installer payload initialization fail

Re: [GENERAL] Configure with Openssl fails

[Joe Conway]

On 10/21/2015 07:31 AM, Michael Hartung wrote:
> being on OS X 10.11 (El Capitain) and trying a VPATH build with plain
> "configure" and "make" works fine.
> 
> /Users/me/Documents/workspace/postgres/configure --with-openssl
> --with-includes=/usr/local/ssl/include/openssl
> --with-libraries=/usr/local/ssl/lib
> 
> fails and leads to:
> 
> ...
> checking openssl/ssl.h usability... no
> checking openssl/ssl.h presence... no
> checking for openssl/ssl.h... no
> configure: error: header file  is required for OpenSSL
> 
> Any ideas...?

Just a guess, but if indeed ssl.h is located in
   /usr/local/ssl/include/openssl
based on the error message I would try:
  --with-includes=/usr/local/ssl/include

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

[GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

Hi,

We have run into some corruption in one of our production tables. We know
the cause (a compute node was moved), but now we need to fix the data. We
have backups, but at this point they are nearly a day old, so recovering
from them is a last-resort and will incur significant downtime.
We are running 9.3.9

Following the steps at
http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html

I get the following output for ctid, id, other_id, tstamp:

 (690651,42) |318698967 |   347978007 | 2015-10-20 01:55:41.757+00
 (690651,43) |318698968 |   347978008 | 2015-10-20 01:55:41.663+00
 (690651,44) |318698969 |   347978009 | 2015-10-20 01:55:42.005+00
ERROR:  invalid page in block 1226710 of relation base/16750/27244

It appears 690652 is what would be dd'd if that's the route we take. Is
that accurate?


Because the message indicates the corruption is in the table's page, not
the page header, according to the docs zero_damaged_pages probably won't
work.

What are my options?

1). Enable zero_damaged_pages and execute VACUUM FREEZE (and hope).
2). dd the block(s) using the output of the ctid query above.

It is multi-gigabyte table that is extremely heavily used (100's to 1000's
of queries per second) so a VACUUM FULL or CLUSTER are options we'd really
like to avoid if possible. The database is about 250GB, not huge, but big
enough that slaves and backups are time consuming to redo, or recover from.

Will attempting zero_damaged_pages cause any harm as the first step (other
than the obvious destruction of any bad pages)?


Is this the correct command if option #2 is chosen? Can it be executed
against a running cluster?
dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1
conv=notrunc



Thanks,

Bricklen

[GENERAL] temporary indexes?

[Jonathan Vanasco]

I couldn't find any mention of this on the archives...

Have the project maintainers ever considered extending CREATE INDEX to support 
"temporary" indexes like CREATE TEMPORARY TABLE?

When creating temporary tables for analytics/reporting, I've noticed that I 
often need to create (then drop) indexes on regular tables.  Temporary indexes 
seemed like a natural fit here, so i was wondering if there was any reason why 
they're not supported (other than no one wanted 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] ERROR: invalid page in block 1226710 of relation base/16750/27244

[Tom Lane]

bricklen  writes:
> We have run into some corruption in one of our production tables. We know
> the cause (a compute node was moved), but now we need to fix the data. We
> have backups, but at this point they are nearly a day old, so recovering
> from them is a last-resort and will incur significant downtime.
> We are running 9.3.9

> Following the steps at
> http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html

> I get the following output for ctid, id, other_id, tstamp:

>  (690651,42) |318698967 |   347978007 | 2015-10-20 01:55:41.757+00
>  (690651,43) |318698968 |   347978008 | 2015-10-20 01:55:41.663+00
>  (690651,44) |318698969 |   347978009 | 2015-10-20 01:55:42.005+00
> ERROR:  invalid page in block 1226710 of relation base/16750/27244

> It appears 690652 is what would be dd'd if that's the route we take. Is
> that accurate?

I'm confused by the block mentioned in the error message not having
anything to do with the TID sequence.  I wonder whether it refers to an
index not the table proper.  What query were you using to get this output,
exactly?  Have you confirmed which relation has relfilenode 27244?

> Because the message indicates the corruption is in the table's page, not
> the page header, according to the docs zero_damaged_pages probably won't
> work.

I should think that zero_damaged_pages would work fine, if the problem
is indeed in the base table.  But if it's in an index, a REINDEX would be
a better answer.

Another thing to keep in mind here, if you've got replication slaves,
is that I'm not sure whether the effects of zero_damaged_pages would
propagate to slaves.  Have you investigated the possibility that some
slave has an uncorrupt copy that you could dd into place in the master?

> Is this the correct command if option #2 is chosen? Can it be executed
> against a running cluster?
> dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1
> conv=notrunc

Uh, no, you're not accounting for the fact that such an offset wouldn't be
in the first segment file of the relation.

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

Re: [GENERAL] temporary indexes?

[Adrian Klaver]

On 10/21/2015 11:43 AM, Jonathan Vanasco wrote:

I couldn't find any mention of this on the archives...

Have the project maintainers ever considered extending CREATE INDEX to support 
"temporary" indexes like CREATE TEMPORARY TABLE?

When creating temporary tables for analytics/reporting, I've noticed that I 
often need to create (then drop) indexes on regular tables.  Temporary indexes 
seemed like a natural fit here, so i was wondering if there was any reason why 
they're not supported (other than no one wanted it!)


Something like this?:

aklaver@test=> create temporary table temp_test(id int, fld_1 varchar);
CREATE TABLE

aklaver@test=> create index temp_idx on temp_test(fld_1);
CREATE INDEX

aklaver@test=> \d temp_test
  Table "pg_temp_2.temp_test"
 Column |   Type| Modifiers
+---+---
 id | integer   |
 fld_1  | character varying |
Indexes:
"temp_idx" btree (fld_1)






--
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] [ADMIN] $libdir/mysql_fdw

[Ryan King - NOAA Affiliate]

Hi Devrim, I have already installed that:
"Package mysql_fdw_94-2.0.1-1.rhel6.x86_64 already installed and latest
version..."
We're looking into some other options though. Thanks though.

Ryan King
Internet Dissemination Group, Kansas City
Shared Infrastructure Services Branch
National Weather Service
Contractor / Ace Info Solutions, Inc.

On Wed, Oct 21, 2015 at 6:04 AM, Devrim GÜNDÜZ  wrote:

>
> Hi,
>
> On Thu, 2015-08-27 at 12:46 -0500, Ryan King - NOAA Affiliate wrote:
> > ERROR:  could not access file "$libdir/mysql_fdw": No such file or
> > directory
> >
> > dbname=# CREATE EXTENSION mysql_fdw;
> >
> > ERROR:  could not open extension control file
> > "/usr/pgsql-9.4/share/extension/mysql_fdw.control": No such file or
> > directory
> >
> > What needs to be installed for this to work?
>
> Looks like you installed PostgreSQL using the community RPMs -- so make
> sure that you used the RPM of mysql_fdw:
>
> yum install mysql_fdw_94
>
> would do the trick.
>
> Regards,
> --
> Devrim GÜNDÜZ
> Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
> Twitter: @DevrimGunduz , @DevrimGunduzTR
>
>
>

Re: [GENERAL] temporary indexes?

[Jeff Janes]

On Wed, Oct 21, 2015 at 11:50 AM, Adrian Klaver 
wrote:

> On 10/21/2015 11:43 AM, Jonathan Vanasco wrote:
>
>> I couldn't find any mention of this on the archives...
>>
>> Have the project maintainers ever considered extending CREATE INDEX to
>> support "temporary" indexes like CREATE TEMPORARY TABLE?
>>
>> When creating temporary tables for analytics/reporting, I've noticed that
>> I often need to create (then drop) indexes on regular tables.  Temporary
>> indexes seemed like a natural fit here, so i was wondering if there was any
>> reason why they're not supported (other than no one wanted it!)
>>
>
> Something like this?:
>
> aklaver@test=> create temporary table temp_test(id int, fld_1 varchar);
> CREATE TABLE
>
> aklaver@test=> create index temp_idx on temp_test(fld_1);
> CREATE INDEX
>


I think he means more like:

create temporary table temp_test(id int, fld_1 varchar);
create temporary index on permanent_table (fld_1);

select something from temp_test join permanent_table using (fld_1) where
a=b;
select something_else from temp_test join permanent_table using (fld_1)
where c=d;

Cheers,

Jeff

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

[Scott Mead]

> On Oct 21, 2015, at 14:58, anj patnaik  wrote:
> 
> With the graphical installer, I had a way to create a user. Does it create 
> postgres user by default?

Yeah, it creates both the OS user and the database super-user. 


> 
> Let me know. Thx
> 
>> On Wed, Oct 21, 2015 at 1:43 PM, Scott Mead  wrote:
>> 
>> > On Oct 21, 2015, at 12:28, Adrian Klaver  wrote:
>> >
>> >> On 10/21/2015 09:14 AM, anj patnaik wrote:
>> >> Ok, i am trying to determine why I am getting errors. Is it possible
>> >> that my browser is corrupting the transfer?
>> >
>> > Maybe, though I used FireFox to download also. I would go to wherever the 
>> > file has been downloaded on your computer and delete it and try the 
>> > download again. The file I got was:
>> >
>> > 37548416 Oct 21 09:20 postgresql-9.5.0-beta1-linux-x64.run
>> >
>> > CCing list
>> >>
>> >> I am seeing: Installer payload initialization failed. This is likely due
>> >> to an incomplete or corrupt downloaded file.
>> >>
>> >> I am a Linux newbie. Is there a way to test if the transfers are getting
>> >> corrupted?
>> >>
>> >> after download, did you just change permissions and ran sudo
>> >> ./postgres.run file?
>> >
>> > Yes, so:
>> >
>> > aklaver@killi:~/Downloads> chmod 755 postgresql-9.5.0-beta1-linux-x64.run
>> >
>> > aklaver@killi:~/Downloads> sudo ./postgresql-9.5.0-beta1-linux-x64.run
>> >
>> >>
>> >> The reason for using installer is that it goes through all the steps and
>> >> instantiates a sid.
>> >
>> > What is a sid?
>> >
>> 
>> Postgres isn't like Oracle. Use yum to install, then run
>> 
>> /etc/init.d/postgres9.5 initdb
>> 
>> Then, run:
>> /etc/init.d/postgres9.5 start
>> 
>> 
>>   That's it.  Initdb creates the instance, start - starts it.  No dbca or 
>> other such garbage is needed. Welcome to the easy life :)
>> 
>> 
>> 
>> >>
>> >> The purpose of this installation is for me to have a database to rest
>> >> the restoration of pg_dump.
>> >
>> > You can also get that with a Yum install, see below for more:
>> >
>> > http://www.postgresql.org/download/linux/redhat/
>> >>
>> >> Thank you.
>> >>
>> >> On Wed, Oct 21, 2015 at 12:07 PM, Adrian Klaver
>> >> mailto:adrian.kla...@aklaver.com>> wrote:
>> >>
>> >>On 10/21/2015 08:57 AM, anj patnaik wrote:
>> >>
>> >>I used the same link:
>> >>http://www.enterprisedb.com/products-services-training/pgdownload
>> >>
>> >>I chose /*Version 9.5.0 Beta 1*/ Linux x86-64
>> >>
>> >>
>> >>I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran it. I did
>> >>not actually complete the install as I already have Postgres
>> >>installed  on this machine. This is on openSUSE 13.2, not that it
>> >>should matter.
>> >>
>> >>
>> >>Then, I tried 9.4 for Linux x86-64
>> >>
>> >>Has anyone downloaded/installed within last 2 days? my OS is
>> >>RHEL 6.5
>> >>
>> >>I am using the Linux machine's firefox browser to download.
>> >>
>> >>is there a way to use yum to get the same installer program that
>> >>goes
>> >>through all the steps?
>> >>
>> >>
>> >>No, Yum will not use the installer program, it will use the RH
>> >>native packaging.
>> >>
>> >>Is there a particular reason you want the installer?
>> >>
>> >>Something specific you want to install?
>> >>
>> >>
>> >>Please advise. thanks
>> >>
>> >>On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
>> >>mailto:adrian.kla...@aklaver.com>
>> >>> >>>> wrote:
>> >>
>> >> On 10/20/2015 05:48 PM, anj patnaik wrote:
>> >>
>> >> Several weeks ago, I successfully
>> >> downloaded postgresql-9.4.4-3-linux-x64.run on a Linux
>> >>server.
>> >>
>> >>
>> >> Where did you download from?
>> >>
>> >>
>> >> Today, I attempted to download from the site. I notice
>> >>9.5 is
>> >> there, but
>> >> getting lots of errors:
>> >>
>> >>
>> >> Looks like you are trying to run a *.exe(Windows) file on a
>> >>Linux
>> >> machine, that is the source of the errors.
>> >>
>> >>
>> >> 1) when downloading and running latest
>> >>
>> >> rchive:
>> >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
>> >>
>> >>[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
>> >> End-of-central-directory signature not found.
>> >>Either this
>> >> file is not
>> >> a zipfile, or it constitutes one disk of a multi-part
>> >> archive.  In the
>> >> latter case the central directory and zipfile
>> >>comment will
>> >> be found on
>> >> the last disk(s) of this archive.
>> >> zipinfo:  cannot find zipfile directory in one of
>> >>
>

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

[Adrian Klaver]

On 10/21/2015 11:58 AM, anj patnaik wrote:

With the graphical installer, I had a way to create a user. Does it
create postgres user by default?


Yes. For more information see here:

http://yum.postgresql.org/howtoyum.php

in particular this PDF:

http://yum.postgresql.org/files/PostgreSQL-RPM-Installation-PGDG.pdf



Let me know. Thx

On Wed, Oct 21, 2015 at 1:43 PM, Scott Mead mailto:sco...@openscg.com>> wrote:


> On Oct 21, 2015, at 12:28, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:
>
>> On 10/21/2015 09:14 AM, anj patnaik wrote:
>> Ok, i am trying to determine why I am getting errors. Is it possible
>> that my browser is corrupting the transfer?
>
> Maybe, though I used FireFox to download also. I would go to wherever the 
file has been downloaded on your computer and delete it and try the download 
again. The file I got was:
>
> 37548416 Oct 21 09:20 postgresql-9.5.0-beta1-linux-x64.run
>
> CCing list
>>
>> I am seeing: Installer payload initialization failed. This is likely due
>> to an incomplete or corrupt downloaded file.
>>
>> I am a Linux newbie. Is there a way to test if the transfers are getting
>> corrupted?
>>
>> after download, did you just change permissions and ran sudo
>> ./postgres.run file?
>
> Yes, so:
>
> aklaver@killi:~/Downloads> chmod 755 postgresql-9.5.0-beta1-linux-x64.run
>
> aklaver@killi:~/Downloads> sudo ./postgresql-9.5.0-beta1-linux-x64.run
>
>>
>> The reason for using installer is that it goes through all the steps and
>> instantiates a sid.
>
> What is a sid?
>

Postgres isn't like Oracle. Use yum to install, then run

/etc/init.d/postgres9.5 initdb

Then, run:
/etc/init.d/postgres9.5 start


   That's it.  Initdb creates the instance, start - starts it.  No
dbca or other such garbage is needed. Welcome to the easy life :)



 >>
 >> The purpose of this installation is for me to have a database to
rest
 >> the restoration of pg_dump.
 >
 > You can also get that with a Yum install, see below for more:
 >
 > http://www.postgresql.org/download/linux/redhat/
 >>
 >> Thank you.
 >>
 >> On Wed, Oct 21, 2015 at 12:07 PM, Adrian Klaver
 >> mailto:adrian.kla...@aklaver.com>
>> wrote:
 >>
 >>On 10/21/2015 08:57 AM, anj patnaik wrote:
 >>
 >>I used the same link:
 >> http://www.enterprisedb.com/products-services-training/pgdownload
 >>
 >>I chose /*Version 9.5.0 Beta 1*/ Linux x86-64
 >>
 >>
 >>I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran
it. I did
 >>not actually complete the install as I already have Postgres
 >>installed  on this machine. This is on openSUSE 13.2, not that it
 >>should matter.
 >>
 >>
 >>Then, I tried 9.4 for Linux x86-64
 >>
 >>Has anyone downloaded/installed within last 2 days? my OS is
 >>RHEL 6.5
 >>
 >>I am using the Linux machine's firefox browser to download.
 >>
 >>is there a way to use yum to get the same installer
program that
 >>goes
 >>through all the steps?
 >>
 >>
 >>No, Yum will not use the installer program, it will use the RH
 >>native packaging.
 >>
 >>Is there a particular reason you want the installer?
 >>
 >>Something specific you want to install?
 >>
 >>
 >>Please advise. thanks
 >>
 >>On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
 >>mailto:adrian.kla...@aklaver.com> >
 >>
 >>>
 >> On 10/20/2015 05:48 PM, anj patnaik wrote:
 >>
 >> Several weeks ago, I successfully
 >> downloaded postgresql-9.4.4-3-linux-x64.run on a
Linux
 >>server.
 >>
 >>
 >> Where did you download from?
 >>
 >>
 >> Today, I attempted to download from the site. I
notice
 >>9.5 is
 >> there, but
 >> getting lots of errors:
 >>
 >>
 >> Looks like you are trying to run a *.exe(Windows)
file on a
 >>Linux
 >> machine, that is the source of the errors.
 >>
 >>
 >> 1) when downloading and running latest
 >>
 >> rchive:
 >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
 >>
 >>[/home/apatnaik/Downloads

Re: [GENERAL] trouble downloading postgres 9.4 for RHEL 6.x

[anj patnaik]

With the graphical installer, I had a way to create a user. Does it create
postgres user by default?

Let me know. Thx

On Wed, Oct 21, 2015 at 1:43 PM, Scott Mead  wrote:

>
> > On Oct 21, 2015, at 12:28, Adrian Klaver 
> wrote:
> >
> >> On 10/21/2015 09:14 AM, anj patnaik wrote:
> >> Ok, i am trying to determine why I am getting errors. Is it possible
> >> that my browser is corrupting the transfer?
> >
> > Maybe, though I used FireFox to download also. I would go to wherever
> the file has been downloaded on your computer and delete it and try the
> download again. The file I got was:
> >
> > 37548416 Oct 21 09:20 postgresql-9.5.0-beta1-linux-x64.run
> >
> > CCing list
> >>
> >> I am seeing: Installer payload initialization failed. This is likely due
> >> to an incomplete or corrupt downloaded file.
> >>
> >> I am a Linux newbie. Is there a way to test if the transfers are getting
> >> corrupted?
> >>
> >> after download, did you just change permissions and ran sudo
> >> ./postgres.run file?
> >
> > Yes, so:
> >
> > aklaver@killi:~/Downloads> chmod 755
> postgresql-9.5.0-beta1-linux-x64.run
> >
> > aklaver@killi:~/Downloads> sudo ./postgresql-9.5.0-beta1-linux-x64.run
> >
> >>
> >> The reason for using installer is that it goes through all the steps and
> >> instantiates a sid.
> >
> > What is a sid?
> >
>
> Postgres isn't like Oracle. Use yum to install, then run
>
> /etc/init.d/postgres9.5 initdb
>
> Then, run:
> /etc/init.d/postgres9.5 start
>
>
>   That's it.  Initdb creates the instance, start - starts it.  No dbca or
> other such garbage is needed. Welcome to the easy life :)
>
>
>
> >>
> >> The purpose of this installation is for me to have a database to rest
> >> the restoration of pg_dump.
> >
> > You can also get that with a Yum install, see below for more:
> >
> > http://www.postgresql.org/download/linux/redhat/
> >>
> >> Thank you.
> >>
> >> On Wed, Oct 21, 2015 at 12:07 PM, Adrian Klaver
> >> mailto:adrian.kla...@aklaver.com>> wrote:
> >>
> >>On 10/21/2015 08:57 AM, anj patnaik wrote:
> >>
> >>I used the same link:
> >>
> http://www.enterprisedb.com/products-services-training/pgdownload
> >>
> >>I chose /*Version 9.5.0 Beta 1*/ Linux x86-64
> >>
> >>
> >>I downloaded(postgresql-9.5.0-beta1-linux-x64.run) and ran it. I did
> >>not actually complete the install as I already have Postgres
> >>installed  on this machine. This is on openSUSE 13.2, not that it
> >>should matter.
> >>
> >>
> >>Then, I tried 9.4 for Linux x86-64
> >>
> >>Has anyone downloaded/installed within last 2 days? my OS is
> >>RHEL 6.5
> >>
> >>I am using the Linux machine's firefox browser to download.
> >>
> >>is there a way to use yum to get the same installer program that
> >>goes
> >>through all the steps?
> >>
> >>
> >>No, Yum will not use the installer program, it will use the RH
> >>native packaging.
> >>
> >>Is there a particular reason you want the installer?
> >>
> >>Something specific you want to install?
> >>
> >>
> >>Please advise. thanks
> >>
> >>On Wed, Oct 21, 2015 at 9:08 AM, Adrian Klaver
> >>mailto:adrian.kla...@aklaver.com>
> >> >>>> wrote:
> >>
> >> On 10/20/2015 05:48 PM, anj patnaik wrote:
> >>
> >> Several weeks ago, I successfully
> >> downloaded postgresql-9.4.4-3-linux-x64.run on a Linux
> >>server.
> >>
> >>
> >> Where did you download from?
> >>
> >>
> >> Today, I attempted to download from the site. I notice
> >>9.5 is
> >> there, but
> >> getting lots of errors:
> >>
> >>
> >> Looks like you are trying to run a *.exe(Windows) file on a
> >>Linux
> >> machine, that is the source of the errors.
> >>
> >>
> >> 1) when downloading and running latest
> >>
> >> rchive:
> >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe
> >>
> >>[/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe]
> >> End-of-central-directory signature not found.
> >>Either this
> >> file is not
> >> a zipfile, or it constitutes one disk of a
> multi-part
> >> archive.  In the
> >> latter case the central directory and zipfile
> >>comment will
> >> be found on
> >> the last disk(s) of this archive.
> >> zipinfo:  cannot find zipfile directory in one of
> >>
> >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe or
> >>
> >>
> >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.zip,
> and
> >> cannot find
> >>
> >>/home/apatnaik/Downloads/postgresql-9.4.5-1-windows-x64.exe.ZIP,
> >> 

Re: [GENERAL] temporary indexes?

[Jonathan Vanasco]

On Oct 21, 2015, at 2:59 PM, Jeff Janes wrote:
> I think he means more like:
> 
> create temporary table temp_test(id int, fld_1 varchar);
> create temporary index on permanent_table (fld_1);
> 
> select something from temp_test join permanent_table using (fld_1) where a=b;
> select something_else from temp_test join permanent_table using (fld_1) where 
> c=d;

Yes. That's exactly what I mean:  A "temporary index" on a "permanent table" , 
which expires in the same manner of a "temporary table".




-- 
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] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

Hi Tom,

On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane  wrote:

> bricklen  writes:
> > I get the following output for ctid, id, other_id, tstamp:
>
> >  (690651,42) |318698967 |   347978007 | 2015-10-20 01:55:41.757+00
> >  (690651,43) |318698968 |   347978008 | 2015-10-20 01:55:41.663+00
> >  (690651,44) |318698969 |   347978009 | 2015-10-20 01:55:42.005+00
> > ERROR:  invalid page in block 1226710 of relation base/16750/27244
>
> > It appears 690652 is what would be dd'd if that's the route we take. Is
> > that accurate?
>
> I'm confused by the block mentioned in the error message not having
> anything to do with the TID sequence.  I wonder whether it refers to an
> index not the table proper.  What query were you using to get this output,
> exactly?  Have you confirmed which relation has relfilenode 27244?
>

Yes, it is definitely a table. There was originally an index on that table
which threw the original error (about sibling mismatch). I dropped the
index and attempted to recreate it, which failed. Further investigation led
to discovery of corruption in the table.


>
> > Because the message indicates the corruption is in the table's page, not
> > the page header, according to the docs zero_damaged_pages probably won't
> > work.
>
> I should think that zero_damaged_pages would work fine, if the problem
> is indeed in the base table.
>

I will make note of that.



>
> Another thing to keep in mind here, if you've got replication slaves,
> is that I'm not sure whether the effects of zero_damaged_pages would
> propagate to slaves.  Have you investigated the possibility that some
> slave has an uncorrupt copy that you could dd into place in the master?
>

We do have one uncorrupted slave, and one corrupted. I have a 4 hour
delayed WAL-apply script that runs on the primary slaves in the disaster
recovery data centres, and I stopped that process as soon as I saw the
error about the sibling mismatch on the master. It is a viable candidate to
fail over to, if we can swing a 20+ hour window of data loss. Right now
that is an undesirable option.


>
> > Is this the correct command if option #2 is chosen? Can it be executed
> > against a running cluster?
> > dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1
> > conv=notrunc
>
> Uh, no, you're not accounting for the fact that such an offset wouldn't be
> in the first segment file of the relation.
>

Hmm, I wasn't sure about that. Thanks for confirming that.

As it stands, my next step is going to be a pg_dump of one of the
up-to-date slaves (with corruption) but I will exclude the bad table. Given
that I know the PK id range, I can COPY out the table's contents before and
after the affected data. This way we can at least recover from backup if
things get entirely borked.

The next part of the plan is to create a temporary version of the table
with all data other than the corrupted range, then do some transaction-fu
to rename the tables.


Thank you for your response, and any other insights are gratefully received.


Cheers,

Bricklen

Re: [GENERAL] temporary indexes?

[melvin6925]

What Adrian is saying is that there is no need for "temporary" indexes. You can 
create the idxs on a temp table and they get dropped when you drop the table.


Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone Original 
message From: Adrian Klaver  Date: 
10/21/2015  14:50  (GMT-05:00) To: Jonathan Vanasco , 
PostgreSQL mailing lists  Subject: Re: [GENERAL] 
temporary indexes? 
On 10/21/2015 11:43 AM, Jonathan Vanasco wrote:
> I couldn't find any mention of this on the archives...
>
> Have the project maintainers ever considered extending CREATE INDEX to 
> support "temporary" indexes like CREATE TEMPORARY TABLE?
>
> When creating temporary tables for analytics/reporting, I've noticed that I 
> often need to create (then drop) indexes on regular tables.  Temporary 
> indexes seemed like a natural fit here, so i was wondering if there was any 
> reason why they're not supported (other than no one wanted it!)

Something like this?:

aklaver@test=> create temporary table temp_test(id int, fld_1 varchar);
CREATE TABLE

aklaver@test=> create index temp_idx on temp_test(fld_1);
CREATE INDEX

aklaver@test=> \d temp_test
   Table "pg_temp_2.temp_test"
  Column |   Type    | Modifiers
+---+---
  id | integer   |
  fld_1  | character varying |
Indexes:
 "temp_idx" btree (fld_1)

>


-- 
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] temporary indexes?

[Adrian Klaver]

On 10/21/2015 12:27 PM, Jonathan Vanasco wrote:


On Oct 21, 2015, at 2:59 PM, Jeff Janes wrote:

I think he means more like:

create temporary table temp_test(id int, fld_1 varchar);
create temporary index on permanent_table (fld_1);

select something from temp_test join permanent_table using (fld_1) where a=b;
select something_else from temp_test join permanent_table using (fld_1) where 
c=d;


Yes. That's exactly what I mean:  A "temporary index" on a "permanent table" , which 
expires in the same manner of a "temporary table".


I misunderstood then. The only thing I can think of is to wrap in a 
transaction, though that presents other issues with open transactions 
and/or errors in the transaction.










--
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] ERROR: invalid page in block 1226710 of relation base/16750/27244

[Tom Lane]

bricklen  writes:
> On Wed, Oct 21, 2015 at 11:46 AM, Tom Lane  wrote:
>> I'm confused by the block mentioned in the error message not having
>> anything to do with the TID sequence.  I wonder whether it refers to an
>> index not the table proper.  What query were you using to get this output,
>> exactly?  Have you confirmed which relation has relfilenode 27244?

> Yes, it is definitely a table. There was originally an index on that table
> which threw the original error (about sibling mismatch). I dropped the
> index and attempted to recreate it, which failed. Further investigation led
> to discovery of corruption in the table.

Hm.  There's still something weird about this though.  Maybe there is no
data at all between pages 1226710 and 690651?  Might be worth doing some
poking around with contrib/pageinspect/.

> As it stands, my next step is going to be a pg_dump of one of the
> up-to-date slaves (with corruption) but I will exclude the bad table. Given
> that I know the PK id range, I can COPY out the table's contents before and
> after the affected data. This way we can at least recover from backup if
> things get entirely borked.

Agreed, if you're gonna mess with the table files directly, it's always
smart to have a fallback plan in case you make things worse.

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

Re: [GENERAL] ERROR: invalid page in block 1226710 of relation base/16750/27244

[bricklen]

On Wed, Oct 21, 2015 at 12:52 PM, Tom Lane  wrote:

> bricklen  writes:
> > Yes, it is definitely a table. There was originally an index on that
> table
> > which threw the original error (about sibling mismatch). I dropped the
> > index and attempted to recreate it, which failed. Further investigation
> led
> > to discovery of corruption in the table.
>
> Hm.  There's still something weird about this though.  Maybe there is no
> data at all between pages 1226710 and 690651?  Might be worth doing some
> poking around with contrib/pageinspect/.
>
>
Ah, good idea.

Thanks again!

Re: [GENERAL] temporary indexes?

[Jonathan Vanasco]

On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote:

> I misunderstood then. The only thing I can think of is to wrap in a 
> transaction, though that presents other issues with open transactions and/or 
> errors in the transaction.

I just explicitly drop.  The convenience of an auto-drop would be a nice backup.

Transactions and table-locking issues are probably why temporary indexes don't 
exist.

-- 
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] trouble downloading postgres 9.4 for RHEL 6.x

[John R Pierce]

On 10/21/2015 11:58 AM, anj patnaik wrote:
With the graphical installer, I had a way to create a user. Does it 
create postgres user by default?


after following the steps I gave earlier, do this...

$ sudo -u postgres psql
  postgres=# create user YOURNAME password 'whatever' superuser;
  postgres=# create database YOURNAME owner YOURNAME;
  postgres=# \q
$

and now when logged in as unix user 'YOURNAME', you can connect to psql 
and use all admin functionality directly.   if you want a gui admin 
utility...


$ sudo yum install -y pgadmin3_94

$ pgadmin





--
john r pierce, recycling bits in santa cruz



--
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] temporary indexes?

[Adrian Klaver]

On 10/21/2015 01:28 PM, Jonathan Vanasco wrote:


On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote:


I misunderstood then. The only thing I can think of is to wrap in a 
transaction, though that presents other issues with open transactions and/or 
errors in the transaction.


I just explicitly drop.  The convenience of an auto-drop would be a nice backup.

Transactions and table-locking issues are probably why temporary indexes don't 
exist.



On later versions there is CREATE INDEX CONCURRENTLY which alleviates 
locking issues at the expense of time. I would think the greater issue 
is the time and overhead of building an index for a table of any size 
would eat into 'temporary'. Seems if you are joining temporary tables 
against permanent tables on a regular basis it would pay just to keep 
the indexes on the permanent tables and pay the expense over a longer 
period of time.


--
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

[GENERAL] Migrate whole cluster to utf8

[NTPT]

Hi all

I have a db cluster (around 50GB of data ) in LATIN2  encoding. Now I need 
to dump whole cluster because of upgrade to newer  version of pstgresql. But
I need to  have new cluster created with utf8 encoding  And databases in 
that clusters tooo (with cs_CZ locale)

what is the best /safe practice ?

thanx for help

[GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

I wrote the following simple function to try to learn what happens to a
DECLAREd variable whose assignment comes from an INTO statement where the
query being executed does not return a result.

CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
DECLARE r RECORD;
BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
$$ LANGUAGE plpgsql;

The function returns true. Given that I can't find any explicit reference
in the documentation about the behavior I've just described is it safe to
assume that the current behavior is the expected behavior but it's just not
documented?

Dane

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Thomas Munro]

On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster  wrote:
> I wrote the following simple function to try to learn what happens to a
> DECLAREd variable whose assignment comes from an INTO statement where the
> query being executed does not return a result.
>
> CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
> DECLARE r RECORD;
> BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
> $$ LANGUAGE plpgsql;
>
> The function returns true. Given that I can't find any explicit reference in
> the documentation about the behavior I've just described is it safe to
> assume that the current behavior is the expected behavior but it's just not
> documented?

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"If STRICT is not specified in the INTO clause, then target will be
set to the first row returned by the query, or to nulls if the query
returned no rows."

-- 
Thomas Munro
http://www.enterprisedb.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] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

On Wed, Oct 21, 2015 at 10:23 PM, Thomas Munro <
thomas.mu...@enterprisedb.com> wrote:

> On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster  wrote:
> > I wrote the following simple function to try to learn what happens to a
> > DECLAREd variable whose assignment comes from an INTO statement where the
> > query being executed does not return a result.
> >
> > CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
> > DECLARE r RECORD;
> > BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
> > $$ LANGUAGE plpgsql;
> >
> > The function returns true. Given that I can't find any explicit
> reference in
> > the documentation about the behavior I've just described is it safe to
> > assume that the current behavior is the expected behavior but it's just
> not
> > documented?
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> "If STRICT is not specified in the INTO clause, then target will be
> set to the first row returned by the query, or to nulls if the query
> returned no rows."
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

​Foot removed from mouth.

Dane​

Re: [GENERAL] temporary indexes?

[Andreas Kretschmer]

Jonathan Vanasco  wrote:

> I couldn't find any mention of this on the archives...
> 
> Have the project maintainers ever considered extending CREATE INDEX to
> support "temporary" indexes like CREATE TEMPORARY TABLE?

Not sure if you mean something like this:

http://www.depesz.com/2015/09/07/hypothetical-indexes/


> When creating temporary tables for analytics/reporting, I've noticed
> that I often need to create (then drop) indexes on regular tables.

Than you have to create this index. But the creation of an index is
expensive, maybe it's cheaper do the nalytics/reporting without an
index.

And yes: you can create an indexe concurrently.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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