Re: The Amazon CloudFront distribution is configured to block access from your country.

[Magnus Hagander]

On Sun, Apr 11, 2021 at 5:25 PM Adrian Klaver  wrote:
>
> On 4/11/21 2:53 AM, Stephan Knauss wrote:
> > Hello Felix,
> >
> > On 11.04.2021 02:31, felix.quin...@yahoo.com wrote:
> >> I see no reason why ask elsewhere first, the link for the installer
> >> download is on the Postgresql website, not elsewhere. The most logical
> >> thing to do is ask here first.
> >
> >> Because it is the Postgresql installer.
> >> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> >>
> > It's unfortunate that your download is blocked. As indicated by others I
> > am also wondering why you ask here for community support.
> >
> > You pointed to a download link which is controlled by a private company
> > selling Postgresql services:
> >
> > Read about this company on an independent website:
> > https://en.wikipedia.org/wiki/EnterpriseDB
> >
> > If you are looking for the download links of the PostgreSQL project, you
> > can find it here:
> >
> > https://www.postgresql.org/download/
>
> Which as others have pointed out links to the EDB site for certain
> downloads. As also pointed it is not entirely clear that some of the
> links are to PGDG installs and some are third party.

Yeah, this is definitely something we could work on improving. Would
just adding a sentence after the link saying "This download is
provided externally by EDB" (very much open to input on exactly what
the sentence should be)? Or were you guys thinking in the line that we
should have one of those "you are now leaving postgresql.org"-steps in
between with a second click?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: The Amazon CloudFront distribution is configured to block access from your country.

[David G. Johnston]

On Mon, Apr 12, 2021 at 1:33 AM Magnus Hagander  wrote:

>
> Yeah, this is definitely something we could work on improving. Would
> just adding a sentence after the link saying "This download is
> provided externally by EDB" (very much open to input on exactly what
> the sentence should be)? Or were you guys thinking in the line that we
> should have one of those "you are now leaving postgresql.org"-steps in
> between with a second click?
>
>
1 for the "you are now leaving" option.

David J.

Re: The Amazon CloudFront distribution is configured to block access from your country.

[felix . quintgz]

The entire enterprisedb site is blocked for me. It's an American company, so I 
imagine it's happening.
In the end I ended up downloading it from www.download3k.es. I checked the 
installer certificate and it seems to be correct.

Thank you very much to all


 On Sunday, April 11, 2021, 11:38:43 AM GMT-4, Adrian Klaver 
 wrote:


 On 4/11/21 8:11 AM, felix.quin...@yahoo.com wrote:
> The link https://www.postgresql.org/download/ in the PostgreSQL Downloads 
> section, Windows, redirects to the page 
> https://www.postgresql.org/download/windows/. There are only two links in it 
> and both redirect to the EnterpriseDB site. At no time is it clarified that 
> it is a third party, even the link is outside the third party section.
> On the page https://www.postgresql.org/download/ there are only two more 
> sections, the source code and third parties. Therefore the official source 
> for downloading the installer is the link I put, I checked all the links on 
> the site and nowhere is it offered to download the installer from the 
> PostgreSQL site itself.
> The use of vpn is not legal in some countries, it is an option that can get 
> me in trouble. The only option I have left is to search for a link in google 
> and download it from some dubious source.

Another option, now that you know the situation, is to contact
EDB(https://www.enterprisedb.com/contact) and see if there are other
ways to get the software.

There is also the option of downloading the source:

https://www.postgresql.org/ftp/source/

from the community site and building Postgres.


>
>  On Sunday, April 11, 2021, 05:54:00 AM GMT-4, Stephan Knauss 
> wrote:
>
>  Hello Felix,
>
> On 11.04.2021 02:31, felix.quin...@yahoo.com wrote:
>> I see no reason why ask elsewhere first, the link for the installer download 
>> is on the Postgresql website, not elsewhere. The most logical thing to do is 
>> ask here first.
>
>> Because it is the Postgresql installer. 
>> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
>>
> It's unfortunate that your download is blocked. As indicated by others I
> am also wondering why you ask here for community support.
>
> You pointed to a download link which is controlled by a private company
> selling Postgresql services:
>
> Read about this company on an independent website:
> https://en.wikipedia.org/wiki/EnterpriseDB
>
> If you are looking for the download links of the PostgreSQL project, you
> can find it here:
>
> https://www.postgresql.org/download/
>
>
> Based on your feedback, it is NOT the link listed on the PostgreSQL
> website, but on a 3rd party website. This is why you got the
> recommendation to check with them. You also got hints on how you could
> potentially work around the download restriction by using a VPN.
>
>
> Stephan
>
>


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

RE: Ways to "serialize" result set for later use?

[Kevin Brannen]

From: Adam Brusselback 
Sent: Saturday, April 10, 2021 9:06 PM
To: pgsql-general 
Subject: Ways to "serialize" result set for later use?

Hey there everyone,

I am going through the process of writing my first pgtap tests for my database, 
and I wanted to get some feedback on if my solution seems fine, is just dumb, 
or could be acomplished much easier another way.

So my main problem I was trying to work around, was my tests are written in 
functions and called using runtests(), so using psql and \copy to save test 
data to the filesystem isn't really part of the workflow, but I still needed a 
way to have my "expected" query resultset passed into results_eq 
[https://pgtap.org/documentation.html#results_eq]
 easily within a function body.

I originally manually dumped some "known good" data from a query to csv, and 
built some SELECT ... FROM VALUES (...) statements by hand to do this. That 
obviously sucks.

…

So I really just wanted to see if there is a better way to go about what i'm 
trying to do, does Postgres already support something similar I can harness 
instead of this hack? Or is this really an alright way to go?

This seems more like an application question, but I'll throw something out for 
you to consider…

IMO, you're trying to put pgtap into an area it wasn't really made for. If you 
can make it do what you want, good for you, but I wouldn't try that. Pgtap is 
great for things like:

  *   Does my DB/Schema/Tables/Views/Functions/… exist, have the correct owner, 
etc.
  *   Does each table have the right columns, defaults, constraints, etc.
  *   Testing of simple functions is possible, but probably only really for 
"immutable" stuff, like I insert "x" I better always get "y" out of it.
  *   More DDL stuff like this that's important because you want to know if 
your "alter" script after an upgrade did the right thing.

Checking data (DML), if functions are doing the right things is something we do 
in our code unit tests.

Of course, TMTOWTDI, YMMV, etc. 😊

HTH,
Kevin
.
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: Ways to "serialize" result set for later use?

[Adam Brusselback]

>  Checking data (DML), if functions are doing the right things is
something we do in our code unit tests.

This is exactly what I am writing, unit tests for my code (which is
pl/pgsql). This is an ELT pipeline for my customers to bulk update their
data in my system, with detailed error reporting for any issues
per-row/column. The code is all plpgsql, as are the few tests i've written
so far. pgTAP is my unit testing framework for this process.

So unit testing my company's (vast) database code is something I am just
trying to figure out and get into my workflow, and it didn't feel like I
had to fight too hard with it at this point, other than $subject$. And even
that isn't an issue with my hacky function in place, it just feels a
little...dirty I guess? Was just wanting a gut check if there seemed to be
an obviously better way to get the same results.

>

Re: Reuse of REF Cursor

[Ron]

On 4/11/21 1:02 PM, Abraham, Danny wrote:

2021-04-09 08:00:08.692 IDTERROR:  canceling statement due to statement timeout
2021-04-09 08:00:08.692 IDTCONTEXT:  PL/pgSQL function 
orhpans_active_clean_table(character varying,integer) line 42 at FETCH
 PL/pgSQL function orhpans_active_removal() line 31 at assignment
 PL/pgSQL function ajf_backup(integer) line 39 at assignment

Can a FETCH fail if the table is locked? The FETCH is stuck for the 
 time.

Should I  lock all tables involved with the query?

Any specific time-out on the fetch? Or should I use the general 
statement-timeout?

I mean move from regular programming mode to paranoidic mode

The failure is inconsistent.. Never fails in PG 11.5, but fails in PG9.5.5 
about once a week...

I need a full understanding of the problem in order to force big,slow customers 
to migrate to PG11.5.


9.5.21 would be an important step.  Heck, it might solve the problem.

--
Angular momentum makes the world go 'round.

Re: The Amazon CloudFront distribution is configured to block access from your country.

[Condor]

On 12-04-2021 17:53, felix.quin...@yahoo.com wrote:

The entire enterprisedb site is blocked for me. It's an American
company, so I imagine it's happening.
In the end I ended up downloading it from www.download3k.es. I checked
the installer certificate and it seems to be correct.

Thank you very much to all



The antivirus scan of the site looks amazing. Only a fool would believe 
it...

Download DB only from official site.

Re: The Amazon CloudFront distribution is configured to block access from your country.

[felix . quintgz]

Unfortunately the official site for downloading the installer is blocked for me.
I only had that option and trust what I am downloading. 


 On Monday, April 12, 2021, 03:55:37 PM GMT-4, Condor  wrote:


 On 12-04-2021 17:53, felix.quin...@yahoo.com wrote:
> The entire enterprisedb site is blocked for me. It's an American
> company, so I imagine it's happening.
> In the end I ended up downloading it from www.download3k.es. I checked
> the installer certificate and it seems to be correct.
>
> Thank you very much to all
>

The antivirus scan of the site looks amazing. Only a fool would believe
it...
Download DB only from official site.

Re: Prepare Statement VS Literal Values

[Virendra Kumar]

Thank you for the pointer, here is plan now, it is much better:

testdb=# prepare fooplan(character varying,character varying ,bigint,character 
varying) AS select
testdb-# this_.warehouse_cost_id , this_.asin , this_.base_currency_code,
testdb-# this_.cost , this_.cost_acquisition_date , this_.cost_reference_id ,
testdb-# this_.cost_reference_id_source , this_.created_by , 
this_.creation_date ,
testdb-# this_.gl_product_group , this_.warehouse_fiscal_owner_id ,
testdb-# this_.warehouse_owner_group_id , this_.warehouse_txn_type_id ,
testdb-# this_.item_disposition_code , this_.last_updated , 
this_.last_updated_by ,
testdb-# this_.last_updated_date , this_.on_hand_quantity , 
this_.original_quantity ,
testdb-# this_.record_version_number , this_.warehouse_id  from warehouse_costs 
this_
testdb-# where this_.warehouse_id=$1
testdb-# and this_.asin=$2
testdb-# and this_.warehouse_owner_group_id=$3
testdb-# and this_.item_disposition_code=$4
testdb-# order by this_.cost_acquisition_date asc;
PREPARE
testdb=# explain (analyze, buffers) execute 
fooplan('IMXK','B002LA1D9Y','1','SELLABLE');

  QUERY PLAN
  
--
 Sort  (cost=8.46..8.47 rows=1 width=160) (actual time=0.108..0.110 rows=5 
loops=1)
   Sort Key: cost_acquisition_date
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=8
   ->  Index Scan using i_ic_asin_whse_disp_iog_qty on warehouse_costs this_  
(cost=0.42..8.45 rows=1 width=160) (actual time=0.067..0.080 rows=5 loops=1)
 Index Cond: ((asin = 'B002LA1D9Y'::bpchar) AND (warehouse_id = 
'IMXK'::bpchar) AND ((item_disposition_code)::text = 'SELLABLE'::text) AND 
(warehouse_owner_group_id = '1'::numeric))
 Buffers: shared hit=8
 Execution time: 0.238 ms
(8 rows)

Regards,
Virendra Kumar





On Monday, April 12, 2021, 4:18:01 AM PDT, Vijaykumar Jain 
 wrote: 





I am sorry if i have messed up the email original content.
feel free to ignore my diversion.
i forwarded this mail from some other account to work on the issue.

On Mon, Apr 12, 2021 at 4:45 PM Vijaykumar Jain  
wrote:
> 
> Hi,
> 
> I am not sure I am 100% on this, because I have a small machine at home.
> 
> but reading PostgreSQL: Documentation: 9.6: PREPARE and
> PostgreSQL - general - bpchar, text and indexes (postgresql-archive.org)
> 
> PostgreSQL: Documentation: 9.6: Operators
> 
> I think it might be something to do with the parameters types in the prepared 
> statement and their casting.
> 
> But , the below would be reproducible
> 
> postgres=# create table t(t_id int4, sn_c char(20));
> CREATE TABLE
> postgres=# insert into t select id, 
> chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*9)::int4+1)
>  from generate_series(1, 1) id;
> INSERT 0 1
> postgres=# create index i_t_sn_c on t(sn_c);
> CREATE INDEX
> postgres=# vacuum analyze t;
> VACUUM
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 
> 'AB1234';
>                                   QUERY PLAN
> ---
>  Index Only Scan using i_t_sn_c on t (actual time=0.048..0.048 rows=0 loops=1)
>    Index Cond: (sn_c = 'AB1234'::bpchar)
>    Heap Fetches: 0
>  Planning Time: 0.119 ms
>  Execution Time: 0.059 ms
> (5 rows)
> 
> postgres=#  EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 
> 'AB1234'::text;
>                        QUERY PLAN
> -
>  Seq Scan on t (actual time=3.037..3.037 rows=0 loops=1)
>    Filter: ((sn_c)::text = 'AB1234'::text)
>    Rows Removed by Filter: 1
>  Planning Time: 0.057 ms
>  Execution Time: 3.047 ms
> (5 rows)
> 
> postgres=# prepare qq(text) as SELECT sn_c FROM t WHERE sn_c = $1;  // verify 
> from select * from pg_prepared_statements.
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
>                                           QUERY PLAN
> ---
>  Seq Scan on t  (cost=0.00..224.00 rows=50 width=21) (actual 
> time=3.156..3.157 rows=0 loops=1)
>    Filter: ((sn_c)::text = 'AB1234'::text)
>    Rows Removed by Filter: 1
>  Planning Time: 0.069 ms
>  Execution Time: 3.168 ms
> (5 rows)
> 
> postgres=# prepare qq(text) ^C SELECT sn_c FROM t WHERE sn_c = $1;  // verify 
> from select * from pg_prepared_statements.
> postgres=# deallocate qq;
> DEALLOCATE
> postgres=# prepare qq(bpchar) as SELECT sn_c FROM t WHERE sn_c = $1;
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
>                                                     QUERY PLAN
> ---

Re: The Amazon CloudFront distribution is configured to block access from your country.

[Jan Wieck]

On 4/12/21 5:13 PM, felix.quin...@yahoo.com wrote:

Unfortunately the official site for downloading the installer is blocked for me.
I only had that option and trust what I am downloading.


That wasn't your only option. Other members have suggested that you 
contact EnterpriseDB directly.


Note that CloudFront is just a content service by Amazon Web Services. 
The owner of that content, in this case EnterpriseDB, controls those 
"GeoRestrictions", that you are having trouble with. See



https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/georestrictions.html


for details.

It is possible that this restriction is on purpose and that EDB really 
doesn't want people from your country to download those files. It is 
also possible that this particular download got that restriction by 
mistake, either on EDB's side or on the side of the third-party GeoIP 
service, Amazon is using to determine the origin of your IP address. I 
know a few people at EDB (I did work there in the past). Asking them 
won't do any harm and if all of this is actually by mistake, they will 
be glad you asked.



Regards, Jan

--
Jan Wieck
Postgres User since 1994

Re: How can I insert the image as a blob in the table

[Igor Korot]

Hi, guys,

On Sun, Apr 11, 2021 at 7:08 PM Igor Korot  wrote:
>
> Hi, David,
>
> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
>  wrote:
> >
> > On Sunday, April 11, 2021, Igor Korot  wrote:
> >>
> >> Hi, David,
> >>
> >>
> >> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
> >>  wrote:
> >>
> >> > If you can decide on what client interface you want to use there should 
> >> > be existing resources on the web walking through how to do this using 
> >> > that client interface.
> >>
> >> Something like this:
> >>
> >> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
> >>
> >> Looking to run it from gAdmin in the Terminal.
> >
> >
> > As SQL is executed on the server there is the major issue of the server 
> > being unable to resolve /home on the client machine.  But maybe pgAdmin has 
> > its own way to deal with this.  Have you read its documentation?
>
> I don't know - server is running on the same OSX machine as pgAdmin will be.

So nobody is playing with the images?

Is it even possible - to insert an image as BLOB into the database
using a simple
INSERT INTO query?

Thank you.

>
> Thank you.
>
> >
> > David J.
> >

Re: How can I insert the image as a blob in the table

[Rob Sargent]

>
> On Apr 12, 2021, at 7:29 PM, Igor Korot  wrote:
> 
> Hi, guys,
> 
> On Sun, Apr 11, 2021 at 7:08 PM Igor Korot  > wrote:
>> 
>> Hi, David,
>> 
>> On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
>>  wrote:
>>> 
>>> On Sunday, April 11, 2021, Igor Korot  wrote:
 
 Hi, David,
 
 
 On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
  wrote:
 
> If you can decide on what client interface you want to use there should 
> be existing resources on the web walking through how to do this using 
> that client interface.
 
 Something like this:
 
 INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
 
 Looking to run it from gAdmin in the Terminal.
>>> 
>>> 
>>> As SQL is executed on the server there is the major issue of the server 
>>> being unable to resolve /home on the client machine.  But maybe pgAdmin has 
>>> its own way to deal with this.  Have you read its documentation?
>> 
>> I don't know - server is running on the same OSX machine as pgAdmin will be.
> 
> So nobody is playing with the images?
> 
> Is it even possible - to insert an image as BLOB into the database
> using a simple
> INSERT INTO query?


Many of us ‘play with images’.  We use tools to write to the database.  I’m 
currently using java to write millions of records into the database include 
bytea columns with 50-100K binary data.  What tools do you have at hand?
It’s difficult to get an image into a string like ‘insert into tablename values 
()’

Re: How can I insert the image as a blob in the table

[Ron]

On 4/12/21 8:29 PM, Igor Korot wrote:

Hi, guys,

On Sun, Apr 11, 2021 at 7:08 PM Igor Korot  wrote:

Hi, David,

On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
 wrote:

On Sunday, April 11, 2021, Igor Korot  wrote:

Hi, David,


On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
 wrote:


If you can decide on what client interface you want to use there should be 
existing resources on the web walking through how to do this using that client 
interface.

Something like this:

INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );

Looking to run it from gAdmin in the Terminal.


As SQL is executed on the server there is the major issue of the server being 
unable to resolve /home on the client machine.  But maybe pgAdmin has its own 
way to deal with this.  Have you read its documentation?

I don't know - server is running on the same OSX machine as pgAdmin will be.

So nobody is playing with the images?

Is it even possible - to insert an image as BLOB into the database
using a simple
INSERT INTO query?


psql only understands text input.  Therefore, the image must be text (like 
base64) or hex with a leading "\\x".


--
Angular momentum makes the world go 'round.

Re: How can I insert the image as a blob in the table

[Abdul Qoyyuum]

Have you tried

insert into table(picture) values(lo_import('D:\image.jpg'));

?


On Tue, Apr 13, 2021 at 9:30 AM Igor Korot  wrote:

> Hi, guys,
>
> On Sun, Apr 11, 2021 at 7:08 PM Igor Korot  wrote:
> >
> > Hi, David,
> >
> > On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston
> >  wrote:
> > >
> > > On Sunday, April 11, 2021, Igor Korot  wrote:
> > >>
> > >> Hi, David,
> > >>
> > >>
> > >> On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston
> > >>  wrote:
> > >>
> > >> > If you can decide on what client interface you want to use there
> should be existing resources on the web walking through how to do this
> using that client interface.
> > >>
> > >> Something like this:
> > >>
> > >> INSERT INTO images(image) VALES( loadfile( /home/igor/my_image) );
> > >>
> > >> Looking to run it from gAdmin in the Terminal.
> > >
> > >
> > > As SQL is executed on the server there is the major issue of the
> server being unable to resolve /home on the client machine.  But maybe
> pgAdmin has its own way to deal with this.  Have you read its documentation?
> >
> > I don't know - server is running on the same OSX machine as pgAdmin will
> be.
>
> So nobody is playing with the images?
>
> Is it even possible - to insert an image as BLOB into the database
> using a simple
> INSERT INTO query?
>
> Thank you.
>
> >
> > Thank you.
> >
> > >
> > > David J.
> > >
>
>
>

-- 
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043

Re: How can I insert the image as a blob in the table

[Rob Sargent]

> On Apr 12, 2021, at 8:31 PM, Abdul Qoyyuum  wrote:
> 
> Have you tried
> 
> insert into table(picture) values(lo_import('D:\image.jpg'));
> ?
> 
No, I’m doing some non-standard compression on the way down.

Re: The Amazon CloudFront distribution is configured to block access from your country.

[Adrian Klaver]

On 4/12/21 7:22 PM, Jan Wieck wrote:

On 4/12/21 7:16 PM, Ron wrote:

On 4/12/21 5:33 PM, Jan Wieck wrote:

On 4/12/21 5:13 PM, felix.quin...@yahoo.com wrote:
Unfortunately the official site for downloading the installer is 
blocked for me.

I only had that option and trust what I am downloading.


That wasn't your only option. Other members have suggested that you 
contact EnterpriseDB directly.


Note that CloudFront is just a content service by Amazon Web 
Services. The owner of that content, in this case EnterpriseDB, 
controls those "GeoRestrictions", that you are having trouble with. See


https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/georestrictions.html 



for details.

It is possible that this restriction is on purpose and that EDB 
really doesn't want people from your country to download those files. 
It is also possible that this particular download got that 
restriction by mistake, either on EDB's side or on the side of the 
third-party GeoIP service, Amazon is using to determine the origin of 
your IP address. I know a few people at EDB (I did work there in the 
past). Asking them won't do any harm and if all of this is actually 
by mistake, they will be glad you asked.


It's quite possible that EDB added the restriction because OP is from a
country on the US-restricted list (and that country also bans VPNs).


It is, which would change "EDB really doesn't want" into "EDB is forced 
by stupid export restrictions, written by stupid lawyers, who have no 
clue what they are making laws about".


While I can appreciate frustrations with legal systems, making a blanket 
statement like the above without the facts in hand is not helpful either.




Yeah, I agree, that is indeed "quite possible".


Regards, Jan




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