Thread: Notes or comments on columns in a table

Notes or comments on columns in a table

From
"Campbell, Lance"
Date:

PostgreSQL 9.2

When creating a table or altering a column of a table, is there a way to add notes on a column?

 

If not:

I have a new enhancement request.  When creating a table or altering a column, it would be nice if a user could add “comments” or “notes” to a column.  This note would only appear when doing “\d table_name”. 

 

Why?  I was adding a new column to a table called “type”.  Normally I only add types as characters even though integers are much better for indexing.  The problem with integers is I never remember what value means what.  Does a 1 mean directory or file.  So I end up creating “type” as a character so I can do a quick SELECT DISTINCT to figure out the options.  I would use integers if I could do \d table_name to see in the notes section what values meant.  I would think other people run into this issue all the time.

 

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

University of Illinois at Urbana-Champaign logo

 

 

Attachment

Re: Notes or comments on columns in a table

From
Thomas Kellerer
Date:
Campbell, Lance wrote on 14.02.2013 20:09:
> PostgreSQL 9.2
>
> When creating a table or altering a column of a table, is there a way
> to add notes on a column?
>

comment on table foo is 'the foo table';
comment on column foo.bar is 'the bar column';

http://www.postgresql.org/docs/current/static/sql-comment.html

Re: Notes or comments on columns in a table

From
Craig James
Date:


On Thu, Feb 14, 2013 at 11:09 AM, Campbell, Lance <lance@illinois.edu> wrote:

PostgreSQL 9.2

When creating a table or altering a column of a table, is there a way to add notes on a column?

 

If not:

I have a new enhancement request.  When creating a table or altering a column, it would be nice if a user could add “comments” or “notes” to a column.  This note would only appear when doing “\d table_name”. 

 

Why?  I was adding a new column to a table called “type”.  Normally I only add types as characters even though integers are much better for indexing.  The problem with integers is I never remember what value means what.  Does a 1 mean directory or file.  So I end up creating “type” as a character so I can do a quick SELECT DISTINCT to figure out the options.  I would use integers if I could do \d table_name to see in the notes section what values meant.  I would think other people run into this issue all the time.


You should use an enumerated type definition.

create type my_file_type (
  'File',
  'Directory',
  'SymLink')

create table foo (
   id integer primary key,
   filetype my_file_type
)

insert into foo(1, 'File');

select * from foo;
  1  File

... and so forth.

Craig

 

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

University of Illinois at Urbana-Champaign logo

 

 


Attachment

Re: Notes or comments on columns in a table

From
"Campbell, Lance"
Date:
I got it to work!  You use the COMMENT in conjunction with \d+.  Very nice.

PostgreSQL Development Team,
Thank you so much for having the COMMENT feature in PostgreSQL.  This is great!


Thanks,

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382




-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas Kellerer
Sent: Thursday, February 14, 2013 2:10 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Notes or comments on columns in a table

Campbell, Lance wrote on 14.02.2013 20:09:
> PostgreSQL 9.2
>
> When creating a table or altering a column of a table, is there a way
> to add notes on a column?
>

comment on table foo is 'the foo table'; comment on column foo.bar is 'the bar column';

http://www.postgresql.org/docs/current/static/sql-comment.html



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


Pg 9.1 master-slave replication

From
Viktor
Date:
Hello,

Could explain somebody what will happen, if the slave server fails ?
Will it affect the master functionality/availability ?

I'm using simple master-slave replication with 2 servers.


--
Best regards


Re: Pg 9.1 master-slave replication

From
"Gilberto Castillo"
Date:

>
> Hello,
>
> Could explain somebody what will happen, if the slave server fails ?
> Will it affect the master functionality/availability ?

nothing!!! the Master continuo work. ;-)))

> I'm using simple master-slave replication with 2 servers.
>
>
> --
> Best regards
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
> running at host imx2.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>
>


Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

Re: Pg 9.1 master-slave replication

From
Viktor
Date:
Ook, thanks =)

On 2/20/2013 5:56 PM, Gilberto Castillo wrote:

Hello,

Could explain somebody what will happen, if the slave server fails ?
Will it affect the master functionality/availability ?
nothing!!! the Master continuo work. ;-)))

I'm using simple master-slave replication with 2 servers.


--
Best regards


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
running at host imx2.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>


Saludos,
Gilberto Castillo
La Habana, Cuba


--- 
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

Re: Pg 9.1 master-slave replication

From
Prashanth Ranjalkar
Date:
It depends on the type of replication is used.

If it's a slony replication then master continues to work and will catch up when slave is available.

However if the Streaming replication is used, Matser continues to work if slave is unavailable till it finds space for WAL archiving on master, depends on the archieving strategy that implemented while configuring replication. WAL's keep accumulating on the master server till it applies to Slave server after it is available. As long as it find space on master, there is no impact but once it fills all the space, master may go down.
 
Thanks & Regards,
 
Prashanth Ranjalkar
Database Consultant & Architect


On Wed, Feb 20, 2013 at 9:34 PM, Viktor <viktor@okservers.eu> wrote:
Ook, thanks =)


On 2/20/2013 5:56 PM, Gilberto Castillo wrote:
Hello,

Could explain somebody what will happen, if the slave server fails ?
Will it affect the master functionality/availability ?
nothing!!! the Master continuo work. ;-)))

I'm using simple master-slave replication with 2 servers.


--
Best regards


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
running at host imx2.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

Saludos,
Gilberto Castillo
La Habana, Cuba


--- 
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu


Re: Pg 9.1 master-slave replication

From
Kevin Grittner
Date:
Prashanth Ranjalkar <prashant.ranjalkar@gmail.com> wrote:

>> Hello, Could explain somebody what will happen, if the slave

> It depends on the type of replication is used.
>
> If it's a slony replication then master continues to work and
> will catch up when slave is available.
>
> However if the Streaming replication is used, [...] once it fills
> all the space, master may go down.

That strikes me a false distinction -- Slony also stores data to be
replicated until the slave becomes available.  Either way you can
control where that is stored, and you need to watch out for space
problems on an extended outage of a replica.

An issue I don't think I've seen mentioned is that if you use
synchronous replication you are telling PostgreSQL not to return an
indication of success for a data-modifying transaction until the
work of that transaction has been persisted on at least one
replica.  To avoid stalls on the master, you may want to define
multiple synchronous replicas, so that when one goes down you keep
running without DBA intervention.

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