Re: Accessing Postgres Server and database from other Machine

[Paul Förster]

Hi Hemil,

> On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:
> 
> Did you restart postgres after changing pg_hba.conf?

that shouldn't be necessary for changes in pg_hba.conf. Just do either on the 
command line:

$ pg_ctl reload

or from psql:

postgres=# select pg_reload_conf();

You can then see the effective result immediately in pg_hab_file_rules:

postgres=# table pg_hba_file_rules;

Hope this helps.

Cheers,
Paul

Re: Alter the column data type of the large data volume table.

[charles meng]

Hi Kevin,

This sounds like a good idea, I will work hard on this idea and let you
know the result.

Most appreciated.

Kevin Brannen  于2020年12月5日周六 上午12:04写道：

> *>From:* Olivier Gautherot 
>
> >>5) If you're brave enough, convert your current table as a partition
> (rename it to something like table_hist), duplicate the table model under
> the same name as now (adjusting the primary key type) and set the INHERITS
> on the primary key range. The inheritance should take care of the type
> conversion (haven't tried it but it's worth a try). If it works, you will
> reach your goal without downtime or significant overhead.
>
> >Sorry, just tried this one and it failed: type mismatch.
>
>
>
> Seems like a sound idea in general. I’d probably rename the tables, let’s
> call them “big_hist” for the old big table and “big_split” for the new
> partitioned table that being used go forward – assuming the original table
> was called “big”. Then create a View that will look at both of those but
> call it the same as the old table, and let the view do a type cast on the
> old key like big_hist.id::bigint so it matches the new type, because the
> view will probably be a union and the type need to match. That way your
> application only has to pause long enough to do a few meta-commands then it
> all can resume, and like Olivier pointed you, you can fix the data by
> moving it from big_hist to big_split in the background as you have time.
>
>
>
> I’d probably put it all in a transaction too:
>
>
>
> Create table … -- all the commands to create your patitioned table
> big_split here
>
> Begin;
>
> Alter table big rename to big_hist;
>
> Create view big select * from big_split union select id::bigint, /* other
> cols */ from big_hist;
>
> Commit;
>
>
>
> Try it on a dev system and if it works you’re off and running. I’d expect
> the view to slow things down a little, but probably not too much if you
> have good indexes. But at least you could transition without major downtime
> and then rename “big_split” back to “big” and drop “big_hist” when you’ve
> finished the transition. I might even be tempted to add a trigger so that
> all new inserts into “big” really go into “big_split” so “big_hist” doesn’t
> grow any more. Your imagination is probably the limit. 😊
>
>
>
> 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: Accessing Postgres Server and database from other Machine

[Alban Hertroys]

> On 5 Dec 2020, at 10:05, Paul Förster  wrote:
> 
> Hi Hemil,
> 
>> On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:
>> 
>> Did you restart postgres after changing pg_hba.conf?
> 
> that shouldn't be necessary for changes in pg_hba.conf. Just do either on the 
> command line:
> 
> $ pg_ctl reload

While you’re in there, also verify that something is listening on the port (see 
below)

$ netstat -an


> or from psql:
> 
> postgres=# select pg_reload_conf();
> 
> You can then see the effective result immediately in pg_hab_file_rules:
> 
> postgres=# table pg_hba_file_rules;

Also:

postgres=# show listen_addresses;

postgres=# show port;

Those will tell you whether the server is listening on the network and on the 
expected port.

Alban Hertroys
--
There is always an exception to always.

Re: Set COLLATE on a session level

[Peter Eisentraut]

On 2020-12-04 17:18, Tom Lane wrote:

There is a SET COLLATION command in the SQL standard that does this.
Someone just has to implement it.  It wouldn't be terribly difficult, I
think.


[ squint... ]  Just because it's in the standard doesn't mean it's a
good idea.  It sounds like this is morally equivalent to a GUC that
changes query semantics.  We have learned painfully that such behaviors
are best avoided, because they break things you didn't expect to break.


I think it would be analogous to the schema search path.

Aw: Re: Set COLLATE on a session level

[Karsten Hilbert]

> > Or a "smart" view. Set a session variable before running the
> > query and have the (one) view return the locale'd data based
> > on the session variable ...
> >
> > set session "mika.current_locale" = 'locale@2_use';
> >
> > and use
> >
> > select current_setting('mika.current_locale')
> >
> > as needed inside the view definition
>
> But the argument that follows COLLATE is an identifier, not a parameter,

ah, true enough

> so it must be hardcoded in the view, just like column names or table names.
> I don't see how you could use the setting to control the COLLATE clause
> through a view.

The view might produce its rows by calling a function which
in turn reads the setting and dynamically constructs and exexcutes
the query needed to produce the locale-dependant rows, no ?  =8-)

Convoluted ?  I guess so ...

Karsten

Aw: Re: Set COLLATE on a session level

[Karsten Hilbert]

> There's [...] but few differences across linguistic sorts.
> These differences tend to be subtle and ignorable by end users.

But _when_ they matter they matter a lot:

Lists of peoples' names in some not-quite expected order
are a major pain to skim over, for example.

OP is in the business of timekeeping the worklife of
people so I guess sorting might matter there.

Karsten

Re: Accessing Postgres Server and database from other Machine

[Adrian Klaver]

On 12/4/20 10:26 PM, Muthukumar.GK wrote:

Hi Nicklas,

   I have added a line "host  all  all 0.0.0.0/0  
md5" in Pg_hba_conf.sample file and restarted postgres server. 


Again, changing lines in the *.sample file will not be of any use. The 
actual pg_hba.conf file is in the same location as I posted previously 
for the postgresql.conf file.


porstgres port has been added in windows firewall (Windows10)as well.  
But my colleague is still facing the below error when connecting my 
postgres server from .net appplication.


Error is - no connection could be made because the target machine 
actively refused it.


Regards
Muthu

On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén > wrote:




On 4 December 2020 17:17:48 CET, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
 >On 12/4/20 8:03 AM, Paul Förster wrote:
 >> Hi Adrian,
 >>
 >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
 >mailto:adrian.kla...@aklaver.com>> wrote:
 >>> That is the wrong file, the *.sample is the giveaway.
 >>
 >> hmmm, I'd rather call it essential reference documentation or
 >template for automation. It's perfectly well suited to automatically
 >strip all comments and then diff the result to ones real world
 >postgresql.conf or some other version postgresql.conf file to find
 >parameters that have been removed or changed with a new PostgreSQL
 >version. This is highly useful for planning migrations and have a
quick
 >reference what to check for before actually migrating. So for me this
 >is much more than just a giveaway.
 >>
 >
 >Yes, but for changing the behavior of a running instance it is the
 >wrong
 >file and it's extension is a clue.


You will prabably also need to change in tge pg_hba.conf file. At
least in linux there is no entry for connections from the outside
there. You will need a "host" entry accepting any ip address or
specify what ip your collegue is connecting from if possible. I have
missed this step a few times and banged my head.

/Nicklas


On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén > wrote:




On 4 December 2020 17:17:48 CET, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
 >On 12/4/20 8:03 AM, Paul Förster wrote:
 >> Hi Adrian,
 >>
 >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
 >mailto:adrian.kla...@aklaver.com>> wrote:
 >>> That is the wrong file, the *.sample is the giveaway.
 >>
 >> hmmm, I'd rather call it essential reference documentation or
 >template for automation. It's perfectly well suited to automatically
 >strip all comments and then diff the result to ones real world
 >postgresql.conf or some other version postgresql.conf file to find
 >parameters that have been removed or changed with a new PostgreSQL
 >version. This is highly useful for planning migrations and have a
quick
 >reference what to check for before actually migrating. So for me this
 >is much more than just a giveaway.
 >>
 >
 >Yes, but for changing the behavior of a running instance it is the
 >wrong
 >file and it's extension is a clue.


You will prabably also need to change in tge pg_hba.conf file. At
least in linux there is no entry for connections from the outside
there. You will need a "host" entry accepting any ip address or
specify what ip your collegue is connecting from if possible. I have
missed this step a few times and banged my head.

/Nicklas




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

Re: Accessing Postgres Server and database from other Machine

[Adrian Klaver]

On 12/5/20 1:05 AM, Paul Förster wrote:

Hi Hemil,


On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:

Did you restart postgres after changing pg_hba.conf?


that shouldn't be necessary for changes in pg_hba.conf. Just do either on the 
command line:

$ pg_ctl reload

or from psql:

postgres=# select pg_reload_conf();

You can then see the effective result immediately in pg_hab_file_rules:

postgres=# table pg_hba_file_rules;

Hope this helps.


Given that the OP changed pg_hba.conf.sample, probably not:).



Cheers,
Paul




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

Re: Accessing Postgres Server and database from other Machine

[Paul Förster]

Hi Adrian,

> On 05. Dec, 2020, at 15:58, Adrian Klaver  wrote:
> 
> Given that the OP changed pg_hba.conf.sample, probably not:).

sorry, I overlook the *.sample part. :D

Cheers,
Paul

Using a boolean column with IF / THEN

[Alexander Farber]

Good evening,

hopefully my question is not too stupid, but -

in a 13.1 database I have a words_users table with a boolean column:

-- the user is not allowed to chat or change the motto
muted boolean NOT NULL DEFAULT false,

Currently I check the value as follows, but I wonder if this is the best
way with PL/pgSQL -

IF EXISTS (SELECT 1 FROM words_users
WHERE uid = _uid AND
muted) THEN
RAISE EXCEPTION 'User % is muted', _uid;
END IF;

Or can this be done in a simpler way?

Thanks
Alex

P.S. Here my entire stored function:

CREATE OR REPLACE FUNCTION words_set_motto(
in_social integer,
in_sidtext,
in_auth   text,
in_motto  text
) RETURNS integer AS
$func$
DECLARE
_uid integer;
BEGIN
IF NOT words_valid_user(in_social, in_sid, in_auth) THEN
RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid;
END IF;

_uid := (SELECT uid FROM words_social WHERE social = in_social AND
sid = in_sid);

IF LENGTH(in_motto) > 250 THEN
RAISE EXCEPTION 'Invalid motto by user %', _uid;
END IF;

IF EXISTS (SELECT 1 FROM words_users
WHERE uid = _uid AND
muted) THEN
RAISE EXCEPTION 'User % is muted', _uid;
END IF;

IF (SELECT
COUNT(NULLIF(nice, 0)) -
COUNT(NULLIF(nice, 1))
FROM words_reviews
WHERE uid = _uid) < -20 THEN
RAISE EXCEPTION 'User % can not change motto', _uid;
END IF;

UPDATE words_users
SET motto = in_motto
WHERE uid = _uid;

RETURN _uid;
END
$func$ LANGUAGE plpgsql;

Re: Using a boolean column with IF / THEN

[David G. Johnston]

On Saturday, December 5, 2020, Alexander Farber 
wrote:

> Good evening,
>
> hopefully my question is not too stupid, but -
>
> in a 13.1 database I have a words_users table with a boolean column:
>
> -- the user is not allowed to chat or change the motto
> muted boolean NOT NULL DEFAULT false,
>
> Currently I check the value as follows, but I wonder if this is the best
> way with PL/pgSQL -
>
>
Maybe not “simpler” but for all those checks you could write a single query
that pulls out all the data at once into a record variable and test against
the columns pf that instead of executing multiple queries.

David J.

Re: Set COLLATE on a session level

[Laurenz Albe]

On Sat, 2020-12-05 at 13:12 +0100, Peter Eisentraut wrote:
> On 2020-12-04 17:18, Tom Lane wrote:
> > > There is a SET COLLATION command in the SQL standard that does this.
> > > Someone just has to implement it.  It wouldn't be terribly difficult, I
> > > think.
> > 
> > [ squint... ]  Just because it's in the standard doesn't mean it's a
> > good idea.  It sounds like this is morally equivalent to a GUC that
> > changes query semantics.  We have learned painfully that such behaviors
> > are best avoided, because they break things you didn't expect to break.
> 
> I think it would be analogous to the schema search path.

Usually you notice right away if the "search_path" is wrong, because
relations won't be found.

But with a bad collation set in a session, the problems would be more
subtle. For example, if someone asks you why an index isn't used to
support sorting, you'd always have to remember to ask what collation
has been set in the session.

Yours,
Laurenz Albe

The hidden cost of limit-offset

[孙冰]

The skipped rows by an OFFSET clause have to be computed nevertheless. I am
wondering if there could be any chance to improve, since the computation is
on the *entire* rows rather than on the *criterial* columns.

Consider the following example.

Create a sample table and insert some random data.

create table thing (
  id int generated always as identity,
  tag float default random()
);
insert into thing select from generate_series(1,100);

Let's find the last 10 rows ordered by tag:

explain (analyze, verbose)
select id, pg_sleep(0.1) from thing
order by tag offset 90;

The execution plan is:

Limit (cost=5.77..5.92 rows=10 width=16) (actual time=9210.751..10121.411
rows=10 loops=1)
  Output: id, (pg_sleep('0.1'::double precision)), tag
  -> Result (cost=4.42..5.92 rows=100 width=16) (actual
time=101.251..10121.344 rows=100 loops=1)
Output: id, pg_sleep('0.1'::double precision), tag
-> Sort (cost=4.42..4.67 rows=100 width=12) (actual
time=0.064..0.200 rows=100 loops=1)
  Output: id, tag
  Sort Key: thing.tag
  Sort Method: quicksort Memory: 29kB
  -> Seq Scan on public.thing (cost=0.00..1.10 rows=100
width=12) (actual time=0.012..0.019 rows=100 loops=1)
Output: id, tag
Planning Time: 0.176 ms
Execution Time: 10121.450 ms

Obviously, all the 100 rows are computed before offsetting & limiting. But
something really catches my eye:

   1. the 100 rows oftagare necessary becausetagis the sort key;
   2. the 100 rows ofidare not all necessary but it is fine sinceidand tagare
   fetched from the same relation;
   3. the 100 rows of pg_sleep(0.1) cause the major performance hit
   unnecessarily; pg_sleep(0.1) depends on neithertagnorid.

Could we improve the situation? Maybe the Limit node could be done in
advance before the Result node for id and pg_sleep(0.1)? The execution plan
could be something like:

Result (cost=5.77..5.92 rows=10 width=16)
  Output: id, pg_sleep('0.1'::double precision), tag
  -> Limit (cost=4.42..5.92 rows=10 width=12)
Output: id, tag
-> Sort (cost=4.42..4.67 rows=100 width=12)
  Output: id, tag
  Sort Key: thing.tag
  -> Seq Scan on public.thing (cost=0.00..1.10 rows=100
width=12)
Output: id, tag

Generally, if the Limit process could be executed immediately after the
criterial attributes are computed rather than the full output is evaluated,
pagination by limit-offset could be considerably more performant for broad
use cases. That is, pagination is done by several keys which is usually
static, while a few dynamic but expensive attributes can be computed quite
effectively since the result set is typically very small, by its
(nested-)loop nature.

I don't understand the postgresql internal, but I suspect such a change may
introduce significant work on the planner and executor. From my point view,
skipping everything (or expensive ones) except the criteria in the target
list would greatly improve the usability of OFFSET, and it is definitely
worth the effort.

Best regards.

–

SUN Bing