Re:

[Amul Sul]

On Tue, May 17, 2022 at 9:12 AM Rama Krishnan  wrote:
>
> Hi All,
>
> How to install pg_repack and use?

Check this: https://reorg.github.io/pg_repack/

Regards,
Amul

Re: How to write such a query?

[Amul Sul]

See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html

On Thu, Jan 6, 2022 at 12:10 PM Igor Korot  wrote:
>
> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
> Thank you.
>
>

Re:

[Amul Sul]

On Tue, Dec 14, 2021 at 5:30 PM Dennis  wrote:
>
> Hi,
>
> Can I add custom sqls for regression tests (pg_regress) ?
> If it can be added how?
>

Check this: https://wiki.postgresql.org/wiki/Regression_test_authoring

Regards,
Amul

Re: Max connections reached without max connections reached

[Amul Sul]

On Mon, Dec 6, 2021 at 6:11 AM James Sewell  wrote:
>>
>> Agreed with both points.  What about we add, subxid count and overflow
>> status in LocalPgBackendStatus and through that, we can show in
>> pg_stat_activity.  That way we don't have to report it ever and
>> whenever the user is running pg_stat_activity they can fetch it
>> directly from "proc->subxidStatus", along with fetching the proc.xid
>> and proc.xmin.  Does this make sense?
>
>
> Not sure about the overhead and performance aspects, but I like this solution 
> from a functional point of view.
>

+1, I too like the idea. The patch doesn't seem to be doing any heavy
lifting, I think that much overhead should be acceptable.

Regards,
Amul

Re: Sizing PostgreSQL VM server sizing

[Amul Sul]

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
might help.

Regards,
Amul



On Tue, Aug 11, 2020 at 9:49 PM Samarendra Sahoo
 wrote:
>
> Dear all,
> We are getting ready to install in production and would like to know what are 
> key considerations and how do we use them to provision VMs for the same?
>
> Thanks a lot!

Re: HASH partitioning not working properly

[Amul Sul]

On Fri, Jun 19, 2020 at 3:50 PM Srinivasa T N  wrote:
>
>
>
> On Fri, Jun 19, 2020 at 3:09 PM Amul Sul  wrote:
>>
>> On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N  wrote:
>> >
>> > On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe  
>> > wrote:
>> >>
>> >> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
>> >> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley  
>> >> > wrote:
>> >> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
>> >> > > >After seeing the below, I feel partitioning is not working 
>> >> > > > properly or it maybe case that my understanding is wrong.  Can 
>> >> > > > somebody explain me what is happening?
>> >> > >
>> >> > > It's your understanding that's not correct.  The value of is passed
>> >> > > through a hash function and the partition is selected based partition
>> >> > > matching the remainder value after dividing the return value of the
>> >> > > hash function by the largest modulus of any partition.
>> >> > >
>> >> > > That might surprise you, but how would you select which partition a
>> >> > > varchar value should go into if you didn't use a hash function.
>> >> > >
>> >> > > David
>> >> >
>> >> > How can I see the output of hash function that is used internally?
>> >>
>> >> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>> >>
>> >> Yours,
>> >> Laurenz Albe
>> >> --
>> >> Cybertec | https://www.cybertec-postgresql.com
>> >>
>> > I guess output formatting is wrong, any help?
>> >
>> >  postgres=# select pg_catalog.hashint4(7);
>> >   hashint4
>> > 
>> >  -978793473
>> > (1 row)
>> >
>> Instead of direct hash function, the easiest way to use
>> satisfies_hash_partition() what is used in defining hash
>> partitioning constraint.
>>
>> You can see the partition constraint by description partition table i.e.
>>  use \d+ busbar_version5.
>>
>> Regards,
>> Amul
>
>
> Sorry, I did not get you.
>
> My current \d+ is
>
> postgres=# \d+ busbar_version6;
> Table "test.busbar_version6"
>   Column  |  Type   | Collation | Nullable | Default | Storage | Stats target 
> |
> Description
> --+-+---+--+-+-+--+-
> 
>  objectid | integer |   |  | | plain   |  
> |
>  ver_id   | integer |   |  | | plain   |  
> |
> Partition of: busbar_version FOR VALUES WITH (modulus 10, remainder 6)
> Partition constraint: satisfies_hash_partition('16397'::oid, 10, 6, ver_id)
> Access method: heap
>
By executing "SELECT satisfies_hash_partition('16397'::oid, 10, 6, ) "
will tell you whether  fits in the partition having modulus 10 and
remainder 6 or not.

Regards,
Amul

Re: HASH partitioning not working properly

[Amul Sul]

On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N  wrote:
>
> On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe  
> wrote:
>>
>> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
>> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley  wrote:
>> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
>> > > >After seeing the below, I feel partitioning is not working properly 
>> > > > or it maybe case that my understanding is wrong.  Can somebody explain 
>> > > > me what is happening?
>> > >
>> > > It's your understanding that's not correct.  The value of is passed
>> > > through a hash function and the partition is selected based partition
>> > > matching the remainder value after dividing the return value of the
>> > > hash function by the largest modulus of any partition.
>> > >
>> > > That might surprise you, but how would you select which partition a
>> > > varchar value should go into if you didn't use a hash function.
>> > >
>> > > David
>> >
>> > How can I see the output of hash function that is used internally?
>>
>> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
> I guess output formatting is wrong, any help?
>
>  postgres=# select pg_catalog.hashint4(7);
>   hashint4
> 
>  -978793473
> (1 row)
>
Instead of direct hash function, the easiest way to use
satisfies_hash_partition() what is used in defining hash
partitioning constraint.

You can see the partition constraint by description partition table i.e.
 use \d+ busbar_version5.

Regards,
Amul

Re: Table partitioning with sequence field in postgresql12

[amul sul]

On Thu, Jun 18, 2020 at 12:22 PM Srinivasa T N  wrote:
>
> Hi,
>I have a parent table with one of the field as ""gid" int4 DEFAULT 
> nextval('"ami_smart_new".aoi_boundary_gid_seq'::regclass)".
>
>I create child tables which inherit parent and use hash partition.  When I 
> directly insert into child tables, will there be any race condition causing 
> two child tables getting the same sequence value for gid?
>
if "gid" is the partitioning key and assuming you are using declarative
partitioning[1], then it won't be possible to have the same "gid" value in two
child partitions.

1] https://www.postgresql.org/docs/current/ddl-partitioning.html

regards,
Amul

Re: Table partitioning with sequence field in postgresql12

[amul sul]

On Thu, Jun 18, 2020 at 12:00 PM Srinivasa T N  wrote:
>
> Hi,
>Partitioning of a table with sequence id as one of its fields is supported 
> in postgresql12?
>
Could you please elaborate on your case a bit more?

Regards,
Amul

Re: Can we have multiple tablespaces with in a database.

[amul sul]

On Fri, Feb 21, 2020 at 12:48 PM Daulat Ram 
wrote:

> What are the differences between Oracle and Postgres tablespace.
>
> I hope this[1] wiki page will help you.


> Can we assign tablespace during Postgres schema creation . As I know in
> Oracle we assign the tablespace during user/schema creation.
>
AFAIK, there is no syntax to assign tablespace to a schema.

Regards,
Amul

1] https://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs

Re: Can we have multiple tablespaces with in a database.

[amul sul]

On Fri, Feb 21, 2020 at 11:53 AM Daulat Ram 
wrote:

> That will be great if you  share any doc where it’s mentioned that we
> can’t use multiple tablespace for a single database. I have to assist my
> Dev team regarding tablespaces.
>
>
>
> Also , what are the differences between Oracle and Postgres Tablespacs?
>
>
>
To be honest I don't know anything about Oracle.

Regards,
Amul

Re: Can we have multiple tablespaces with in a database.

[amul sul]

On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram 
wrote:

> Hi Amul ,
>
> Please share the examples how we can create no. of tablespaces for a
> single database and how we can use them.
>
> As I know we can create database on tablespace
>
>1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION
>'/mnt/pgdatatest/test/pgdata/conn_tbs';
>2. Create database test tablespace ‘conn_tbs';
>
> Maybe I have misunderstood your question; there is no option to specify
more
than one tablespace for the database, but you can place the objects of that
database to different tablespaces (if options available for that object).
E.g. you can place a table in than conn_tbs tablespace.

If option is not specified then by default that object will be created
in conn_tbs.

Regards,
Amul

Re: Can we have multiple tablespaces with in a database.

[amul sul]

On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram 
wrote:

> Hi Team,
>
>
>
> Can we have multiple tablespaces with in a database in postgres?
>
> Yes.


> Can we have a table on different tablespace same as Oracle?
>
Yes -- specify TABLESPACE option while creating that table.

Regards,
Amul

Re: connection error

[amul sul]

On Fri, Sep 7, 2018 at 5:10 PM amul sul  wrote:
>
> Do you have following entry as well?
>
> # IPv6 local connections:
> hostall all ::1/128 trust
>
> I hope "listen_addresses " in postgresql.conf is on default setting.
>

Sorry, I misread -- your local psql connection is fine .

Try setting listen_addresses = '*' in postgresql.conf.

For more detail please have a look to
https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html

Regards,
Amul Sul

Re: connection error

[amul sul]

Do you have following entry as well?

# IPv6 local connections:
hostall all ::1/128 trust

I hope "listen_addresses " in postgresql.conf is on default setting.

Regards,
Amul Sul
On Fri, Sep 7, 2018 at 4:08 PM Sonam Sharma  wrote:
>
> getting below error from application side :
> 
>
> Last acquisition attempt exception:
>
> org.postgresql.util.PSQLException: Connection refused. Check that the 
> hostname and port are correct and that the postmaster is accepting TCP/IP 
> connections.
>
>
> the port no is set correct :
>
> [postgres@lxsqlptgsdev004 data]$ psql -h localhost -p 5432
>
> psql (9.5.14)
>
> Type "help" for help.
>
>
>
> postgres=#
>
>  In pg_hba.conf also:
>
>
> # IPv4 local connections:
>
> hostall all 127.0.0.1/32trust
>
> hostall all 127.0.0.1/23trust
>
> hostall all 172.29.161.53/23trust
>
> hostall all 172.29.161.52/23trust
>
>
> Can someone please help what can be done to resolve this.
>
>
> Thanks,
>
> Sonam

Re: PG11 Hash partitioning and null values in the partition key

[amul sul]

On Fri, Jul 13, 2018, 7:35 PM Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:

> Hi,
>
> given this setup:
>
> create table part2 ( a int, list varchar(10) ) partition by hash (a);
> create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 0);
> create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 1);
> create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3,
> REMAINDER 2);
>
> insert into part2 (a,list) values (1,'aaa');
> insert into part2 (a,list) values (2,'bbb');
> insert into part2 (a,list) values (3,'ccc');
>
> ... it is possible to insert rows like this which will always go to the
> first partition:
>
> insert into part2 (a,list) values (null,'ddd');
> insert into part2 (a,list) values (null,'eee');
> select * from part2_1;
>  a | list
> ---+--
>  2 | bbb
>| ddd
>| eee
> (3 rows)
>
> I suppose this is intended but I could not find anything in the
> documentation about that. Can someone please clarify the logic behind that?
>

The calculated hash value for the null value will be zero, therefore, it
will fall to the partition having remainder zero.

​Regards,
Amul​

Re: How do I copy an element of composite type array into csv file?

[amul sul]

On Wed, May 23, 2018 at 2:05 PM, a <372660...@qq.com> wrote:
> Hi suppose I have composite type and table
>
> create type A as(
> x float8,
> y float8
> );
>
> create table B(
> Ay A[]
> );
>
> insert into B
> values(array[
> (1,2)::A,
> (3,4)::B]
> );
>
> Now I would like to export the first element of table B into an csv file:
>
> COPY B(Ay[1])
> to 'E:/products_199.csv'  DELIMITER ',' CSV HEADER;
>
> The code above reported an syntax error.
>
> How should I do it??
>

Try COPY (SELECT Ay[1] FROM B) to 'E:/products_199.csv'  DELIMITER ','
CSV HEADER;

Regards,
Amul

Re: Can postgresql ignore DST ?

[amul sul]

How about storing timestamp without timezone[1]?

1] https://www.postgresql.org/docs/current/static/datatype-datetime.html

Regards,
Amul

On Thu, Dec 14, 2017 at 10:19 AM, Venkata B Nagothi  wrote:
> Hi All,
>
> I have a question regarding PostgreSQL time zones and daylight savings -
>
> We have been migrating Oracle databases to PostgreSQL and the database we
> are migrating from does not seem to follow daylight savings and it is good
> that postgresql does.
>
> When i query the date columns i get the timezone offsets as +10 and +11
> depending on the date, time etc. Upon querying Oracle, i get all the
> timezone offsets as +11, adding to this, making the application code
> compatible to this will be utterly complex.
> To rule out any application issues, is it possible to get postgresql to
> ignore DST and render all the timestamps with timezone offsets of +11 ?
>
> I know it is weird question and it is not the right thing to do on the
> database, i am just asking in-case we may bump into this requirement in the
> near future.
>
> Any advise is appreciated !
>
>
>
> Regards,
> Venkata B N
>

Re: [GENERAL] Reset Sequence number

[amul sul]

Firstly, anonymous procedures are not supported in PostgreSQL, you need to
embed this block in a plpgsql function[1] body & call that function if you want
reset sequence value manually, or you could use CYCLE option[2] of a
sequence to auto reset.


1] https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html
2] https://www.postgresql.org/docs/devel/static/sql-createsequence.html


Regards,
Amul

On Wed, Nov 22, 2017 at 3:06 PM, Brahmam Eswar  wrote:
>
> we are in process of migrating to postgres and need to reset the sequence
> number with highest value of table key . I want to make it procedural to do
> that as mentioned below,But it's throwing an error .
>
>
>
> DO $$
>  DECLARE
>  SEQ BIGINT;
>  BEGIN
>
>   SEQ:=(SELECT MAX(ID) FROM TABLE_1);
>   ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
>
>  END$$;
>
> Error : syntax error at or near "SEQ"
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.