Re: [GENERAL] Charlotte Postgres User Group

[Don Parris]

Definitely putting this on my calendar.  I have not been aware of other
PostgreSQL users here - would love to meet some other users.

Don

On Wed, Feb 17, 2016 at 10:39 AM, Boyan Botev  wrote:

> If you live near or around Charlotte, please join us for the inaugural
> meeting of the Charlotte PUG on March 1, followed by a second meeting on
> April 11 featuring Bruce Momjian. More information about the two events can
> be found here:
>
> http://www.meetup.com/Charlotte-PostgreSQL-User-Group
>
> Also if you are travelling through Charlotte, NC or you just like to visit
> the "Queen City" you are welcome to stop by an give a talk to the Charlotte
> PUG. Just contact me when you would be in town and I can pencil you in for
> a talk or a presentation.
>
> Thanks,
> Boyan Botev
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

[Don Parris]

On Wed, Jan 27, 2016 at 6:24 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 27, 2016 at 4:03 PM, Don Parris <parri...@gmail.com> wrote:
>
> 


> ​Parameter passing and variables are client-side considerations.  You
> haven't told us how you plan to execute the SQL.
>
> ​IMO the most straight-forward API is a function.  Whether you implement
> that function using a updating CTE or a sequence of separate SQL commands
> is up to you to decide and, if performance matters, benchmark.
>
> Comparing a CTE and function in general doesn't really do much good.
> There are many non-performance concerns involved and the specific usage
> pattern involved will matter greatly in determining overhead.
>
>
Thanks David, that makes sense.  My main front-end - at the moment - is
LibreOffice Base.  With Base, I can probably just create forms using the
underlying tables.  That said, I may also want to write a Python front-end,
in which case I would prefer to let the database do the work it was
designed to do.

Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

[Don Parris]

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus  wrote:

>
>
> > Am I on the right track, or is there some better way to set this up?  My
> > understanding is that views really aren't meant for insert/update
> > operations, and I have seen on the web that using views to insert/update
> is
> > a bit tricky - and still requires a procedure with a rule on the view.
>
> Why not use updatable CTEs?  That's what they're for.
>
>
Sounds great.  But can I use variables, and allow the db user to enter the
data when the CTE is called?  I've used variables in Python scripts for
insert/update/delete, but honestly, I've never used a variable in my
queries in PostgreSQL.  So, instead of 'Joe', as in your example below,
maybe something like first_name?


> WITH update_contact as (
>INSERT INTO contacts ( contact_id, name )
>VALUES ( nexval('contacts_id_seq'), 'Joe' )
>RETURNING contact_id ),
> new_cont_ids AS (
>SELECT contact_id FROM update_contact;
> ),
> insert_phones AS (
>INSERT INTO phones ( phone_id, contact_id, phone_no )
>SELECT ( nextval('phone_id_seq'), contact_id, '415-555-1212' )
>FROM new_cont_ids
>RETURNING phone_id
> ) ...
>
> I think you get the idea.  On 9.3 or later, this is the way to go.
>
>

[GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

[Don Parris]

I have several tables related to people and their contact information, and
want db users to be able to add or update a given person and their
respective contact information in one step, and get all the information
into the correct tables.

I think I am ok with setting the privileges on the tables and columns as
appropriate to allow each group to select, insert and update the
appropriate data, and I can create appropriate views for them to view data
they need to see. However, I am not really sure about the best way to allow
someone to actually insert/update the data.

For instance, given the following tables:
core.category
contact.entity
contact.person
contact.entity_category --linking table between entity and category
contact.person_entity --linking table between entity & person
... --other tables for address and phone contact info

I haven't really given much thought as to how such a procedure might look,
but I'm guessing something along the lines of:
CREATE FUNCTION record_insert() RETURNS integer AS $$
BEGIN
INSERT statements... --need PK from entity & category tables to insert into
entity_category table.
END
$$
language plpgsql;

Ideally, the db user just says "I want to enter Joe Public, and Joe is
affiliated with the Widget Corp entity, and has the phone numbers..."

Am I on the right track, or is there some better way to set this up?  My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to insert/update is
a bit tricky - and still requires a procedure with a rule on the view.


Thanks,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

[GENERAL] Schemas, Roles & Search Path

[Don Parris]

Hi all,

I just want to check my understanding of schemas, search paths and roles in
implementing my database design.

Scenario:
A database with various "modules" (groups of tables & views, etc.), some of
which are shared in common across a given organization, but others are
specific to a given department within the organization.  I am thinking to
use schemas for the department-specific modules.  So, for example:
Schema COMMON module: contains relations for categories, contacts and a few
other items.
FACILITIES module: contains relations for facility management department,
which includes the physical security team.
HR module: HR-specific relations
ACCOUNTING module: Accounting relations - but other departments need access
to certain accounting functions.
The relations within each schema still need appropriate privilege settings,
of course.

I would use group and user roles to manage privileges throughout the
database, so:
Group roles for HR, Accounting, Facilities, Sales, etc.
User roles assigned to appropriate group roles

I *think* I want to set the search path on the group roles so that the
Facilities team can see the COMMON and FACILITIES schemas:

ALTER ROLE fm_users search_path=common, facilities, accounting;


Or do I need to set the search path for each user individually?

ALTER ROLE joe SET search_path=common, facilities, accounting;


I don't see any references to setting the search_path for group roles, so I
assume I need to set this at the user role level.  Is that correct?

My thinking on the design could be off base, but I think this is the
general approach I want.


Thanks!
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

Re: [GENERAL] Schemas, Roles & Search Path

[Don Parris]

On Sat, Jan 9, 2016 at 11:16 AM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> Just a side note. the search_path only sets the priority for resolving
> table locations
> EG: First look in COMMON, then FACILITIES until the table name is found.
>
> However, if you prefix the table name with the actual schema EG:
> COMMON.table, then the table is located directly and search_path is not
> needed.
>


Melvin, thanks for that - confirms what I understand about schemas.


>
> Finally, it is NOT a good idea to use UPPERCASE or CamelCase  for object
> names in PostgreSQL, as PostgreSQL will naturally assume lowercase for all
> objects
> unless they are quoted. So probably you want schema names to be common,
> facilities, etc.
>
>
Thanks.  I was just capitalizing for the e-mail.  I never actually use
upper or camel case in my code.



> On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> Don Parris <parri...@gmail.com> writes:
>> > I *think* I want to set the search path on the group roles so that the
>> > Facilities team can see the COMMON and FACILITIES schemas:
>> > ALTER ROLE fm_users search_path=common, facilities, accounting;
>> >
>> > Or do I need to set the search path for each user individually?
>> > ALTER ROLE joe SET search_path=common, facilities, accounting;
>>
>> The latter.  A session only absorbs ALTER ROLE SET settings for the
>> exact role you're logging in as.  (Otherwise there would be a need
>> for a conflict resolution rule, and it's pretty hard to see how that
>> would work in general for arbitrary settings.)  Role "inheritance"
>> applies to granted privileges only.
>>
>> 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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

Re: [GENERAL] Schemas, Roles & Search Path

[Don Parris]

On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Don Parris <parri...@gmail.com> writes:
> > I *think* I want to set the search path on the group roles so that the
> > Facilities team can see the COMMON and FACILITIES schemas:
> > ALTER ROLE fm_users search_path=common, facilities, accounting;
> >
> > Or do I need to set the search path for each user individually?
> > ALTER ROLE joe SET search_path=common, facilities, accounting;
>
> The latter.  A session only absorbs ALTER ROLE SET settings for the
> exact role you're logging in as.  (Otherwise there would be a need
> for a conflict resolution rule, and it's pretty hard to see how that
> would work in general for arbitrary settings.)  Role "inheritance"
> applies to granted privileges only.
>
> regards, tom lane
>

Thanks Tom, that helps!

Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

Re: [GENERAL] Locale Issue

[Don Parris]

On Thu, Aug 22, 2013 at 3:23 AM, Stuart Bishop stu...@stuartbishop.netwrote:

 On Wed, Aug 21, 2013 at 4:55 PM, Don Parris parri...@gmail.com wrote:

  I did run pg_dropcluster, pg_createcluster (setting locale to C.UTF8) -
 and
  that seemed to work, except that I could not thereafter make remote
  connections (despite resetting the hba and postgres.conf files as they
  should be).

 Rather than chasing locales, I'd look into why you failed here. When
 you install the PostgreSQL packages, it runs pg_createcluster for you.
 If you don't like the locale or encoding you used, you run
 pg_dropcluster and pg_createcluster as you did. The reason why your
 database did not work after doing this is probably obvious from your
 log files.


 Hi Stuart,



Hi Stuart,


Your point is valid and well-taken.  I discovered a couple of other issues
in my poking around:

 The pgsql logs turned up empty (literally), so would have to rely on
system logs for any hints of connection issues.

 It seems my iptables was ruined - I kept getting errors about needing an
update and the software update manager kept giving me errors on kernel
updates.  I could not create iptables rules to open the port.

 I also had problems with my SSH service.

I really suspect the whole connectivity issue was related to updates and
something going South with my iptables setup (among other possible issues).
 I had been able to connect remotely before I replaced the cluster, but not
after, so I just cannot be too sure what ultimately caused the problem.


But I still come back to the locale issue.  I am glad I was on the right
track in replacing the cluster.  Still, how could I have made UTF-8 the
default encoding at install time?  Maybe the very first step on a Kubuntu
system should be to replace the cluster before doing anything else.  Or
maybe there is a locale setting that can be changed to ensure the pgsql
package gets the 'right' cues?



An OpenSUSE 12.3 installation with pgsql 9.2 comes up with a UTF8-encoded
template1.  So why does the (K)Ubuntu package default to an ASCII-encoded
template1?  When I read the fine manual (and it really is pretty good), I
get the impression it is best to let initdb/pg_createcluster take its cue
from the OS locale settings.  If my locale on Kubuntu shows up as UTF-8,
then why the seeming miscue?  I am certain there is a reasonable
explanation, but that eludes me at this point.

Regards,
Don

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] Locale Issue

[Don Parris]

On Thu, Aug 22, 2013 at 11:29 AM, Don Parris parri...@gmail.com wrote:

 On Thu, Aug 22, 2013 at 3:23 AM, Stuart Bishop stu...@stuartbishop.netwrote:

 On Wed, Aug 21, 2013 at 4:55 PM, Don Parris parri...@gmail.com wrote:

 SNIP

 you install the PostgreSQL packages, it runs pg_createcluster for you.
 If you don't like the locale or encoding you used, you run
 pg_dropcluster and pg_createcluster as you did. The reason why your
 database did not work after doing this is probably obvious from your
 log files.


 Hi Stuart,


 SNIP



 But I still come back to the locale issue.  I am glad I was on the right
 track in replacing the cluster.  Still, how could I have made UTF-8 the
 default encoding at install time?  Maybe the very first step on a Kubuntu
 system should be to replace the cluster before doing anything else.  Or
 maybe there is a locale setting that can be changed to ensure the pgsql
 package gets the 'right' cues?



 SNIP
I did find a (bit dated) response on the UbuntuForums site, but assume the
basic advice applies:
http://askubuntu.com/questions/20880/how-do-i-create-a-unicode-databases-in-postgresql-8-4

~#export LANGUAGE=en_US.UTF-8
~#export LANG=en_US.UTF-8
~#export LC_ALL=en_US.UTF-8
~#locale-gen en_US.UTF-8
~#dpkg-reconfigure locales


The recommendation is to change the OS locale settings and use dpkg to
reconfigure locales *before* recreating the cluster.

I really do think the Ubuntu documentation needs to clarify that.

Finally, I am ever more convinced that a recent update effectively blew up
my connectivity.

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] Locale Issue

[Don Parris]

On Thu, Aug 22, 2013 at 5:12 PM, Vincent Veyron vv.li...@wanadoo.fr wrote:

 Le jeudi 22 août 2013 à 11:29 -0400, Don Parris a écrit :

Still, how could I have made UTF-8 the default encoding at install
  time?

 I did several recent installations of Postgresql on Debian Wheezy with
 UTF8 and LATIN9, and my system's encoding was used every time.

 Installation steps outlined below.

 Before you install Postgresql, make sure your systems encoding is set to
 UTF-8, which you can check with :
 locale -a

 If it's not, run :
 dpkg-reconfigure locales

 and select UTF-8 for your language.

 Use apt-get to install Postgresql :
 apt-get install postgresql postgresql-client

 I'm not sure if Kubuntu adds some overhead to the installation process.
 You might want to try a regular debian install and add the KDE desktop
 to it.


Thanks Vincent.  locale showed everything *except* LC_ALL as being
en_US.UTF-8.  LC_ALL was empty.  I can only guess that the LC_ALL setting
may have played a part in the problem.  See my previous e-mail (sent just
a  few minutes before this one).  I may have also been fine if that system
had not lost connectivity.  Trying out things on OpenSUSE 12.3, I do have
SSH connectivity and the Postgres server came up with template1 encoded as
UTF-8 by default.  I'll test out the pgsql remote connectivity soon as well.

[GENERAL] Locale Issue

[Don Parris]

Hi all,

When I install the Kubuntu (13.04) postgresql (9.1) packages, the default
template1 encoding turns out to be ASCII, which is not really what I want.
My OS locale command reveals everything to be en_US.UTF-8, except for
LC_ALL, which is left open.

I am guessing that my best bet is to change the locale settings of my OS in
order to get postgresql server to use a UTF-8 locale setting from the
system.  But which setting should I change?

I did run pg_dropcluster, pg_createcluster (setting locale to C.UTF8) - and
that seemed to work, except that I could not thereafter make remote
connections (despite resetting the hba and postgres.conf files as they
should be).

Thanks!
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] Locale Issue

[Don Parris]

On Wed, Aug 21, 2013 at 10:08 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Don Parris parri...@gmail.com writes:
 SNIP



 initdb will absorb locale/encoding from its environment, unless told
 otherwise through a --locale switch.  So the usual expectation would be
 that it'd work like you want.  Perhaps the Ubuntu packager set LANG=C in
 the postgres user's ~/.profile, or some such?  Poke around a bit in the
 package's files, it shouldn't be too hard to find where the damage is
 being done.

 regards, tom lane


Thanks Tom,

I'll check that out.

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] (SOLVED)How To Install Extension Via Script File?

[Don Parris]

On Sat, Aug 10, 2013 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Chris Travers chris.trav...@gmail.com writes:
  As for whether UTF-8 is the default, it is in many cases, but I remember
  struggling with the fact that a few Linux distros still default to
  SQL-ASCII.  Ultimately this is something of a packaging issue and the
  default may be set at the package level.

 Actually, the default is taken from the locale environment that initdb
 sees.  So it's a question of what the distro initializes LANG to (and
 whether you've changed that, either system-wide or for the postgres user).

 regards, tom lane


I'd like to call this one solved - at least mostly.  I am not sure what
happened before, but when I tried installing the ltree module on template1
previously, it did not seem to make any difference when I created a new
DB.  I could not create an ltree field.  Fast forward to now, and (with a
fresh postgres server) installed ltree on template1 and then connected and
successfully created a test db (using the -e unicode option), along with
a table using the ltree datatype.

This actually resolves the core issue - being able to create new databases
and use the ltree data type.

Two related points remain a bit confusing, but I will read up more and
re-post if I cannot figure them out.

Thanks all!
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] How To Install Extension Via Script File?

[Don Parris]

On Thu, Aug 8, 2013 at 8:42 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Don Parris parri...@gmail.com writes:
  When I try a simple psql -U postgres -W - just to initiate the psql
  session, I get:
  psql: FATAL:  Peer authentication failed for user postgres

  It's like my regular user cannot connect as the postgres user.

 You're right, it can't, if you've selected peer authentication in
 pg_hba.conf.  You'd need to use some other auth method, perhaps
 password-based auth, if you want this to work.  Read up on auth methods
 in the fine manual.

  However, this works (with me just typing my password for sudo):
  donp@wiesbaden:~$ sudo -u postgres psql -U postgres
  [sudo] password for donp:

 Sure, because then psql is launched as the postgres OS user, and peer auth
 will let that user connect as the postgres DB user.

 regards, tom lane


A...  now I understand.  Thanks Tom!  That should really help!  I'll
check that out.

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] How To Install Extension Via Script File?

[Don Parris]

On Fri, Aug 9, 2013 at 11:49 AM, Quentin Hartman 
qhart...@direwolfdigital.com wrote:

 A bit of an aside, but you also might want to change that CREATE
 EXTENSION ltree; to

 CREATE EXTENSION IF NOT EXISTS ltree;

 That way your script won't error out if the extension is already enabled.


Thanks for that, Quentin.  I'll be sure to do that.

[GENERAL] How To Install Extension Via Script File?

[Don Parris]

Hi all,

I have a database that uses the ltree extension.  I typically create a new
database like so (as a normal user), using my script file:

CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

And then su to postgres, login and install the ltree extension on mydb.
Then I logout of my psql instance and re-run the script (as a normal user)
to create the tables  views on mydb.  I comment out the table/view
creation portion until I finish the first couple steps, and then uncomment
the tables and views on the 2nd run.  Otherwise the script will fail
because the ltree extension has to be installed as a superuser.

I want a script something like:
CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';
\c mydb
CREATE EXTENSION ltree;
CREATE TABLE mytable(myfields);
rinse, repeat for additional tables and views.

And to be able to run it from the Bash prompt (as securely as possible).

I thought I could add the commands and run the create script by doing:
sudo -u postgres psql -U user -W -d mydb
--file=/home/user/dev/mydb_create.sql

I thought that, running my script as the superuser, it would have the
privileges necessary to install the extension, but I still got a
permission denied error.

I did create a password for my postgres user (hence the -W option).  And
this is on a local box.

How can I run my script?

Thanks!
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ https://www.xing.com/profile/Don_Parris
GPG Key ID: F5E179BE

Re: [GENERAL] How To Install Extension Via Script File?

[Don Parris]

On Thu, Aug 8, 2013 at 5:44 PM, Thomas Kellerer spam_ea...@gmx.net wrote:

 Don Parris wrote on 08.08.2013 23:13:

  And to be able to run it from the Bash prompt (as securely as possible).

 I thought I could add the commands and run the create script by doing:
 sudo -u postgres psql -U user -W -d mydb --file=/home/user/dev/mydb_**
 create.sql


  I thought that, running my script as the superuser, it would have the
 privileges necessary
 to install the extension, but I still got a permission denied error.


 You are not running the script as superuser because you supplied -U user
 and thus the _script_ is executed
 as user. psql is started as postgres (the Linux user, not the DB user).


My bad - forgot to change the example above to reflect accurately the
user...  I did use the postgres user.


 I don't see the reason for using sudo in the first place, -U is enough:


Fair enough.  But I think you are onto something here below...

 Leave out the sudo, and use:

   psql -U postgres -W -d mydb --file=/home/user/dev/mydb_**create.sql

 Or if you do want to use sudo, the leave out the -U user parameter:

   sudo -u postgres psql -d mydb --file=/home/user/dev/mydb_**create.sql



When I try a simple psql -U postgres -W - just to initiate the psql
session, I get:
psql: FATAL:  Peer authentication failed for user postgres

It's like my regular user cannot connect as the postgres user.

However, this works (with me just typing my password for sudo):
donp@wiesbaden:~$ sudo -u postgres psql -U postgres
[sudo] password for donp:
psql (9.1.9)
Type help for help.

postgres=# \q

Maybe I have some permissions issues?

Thanks,
Don

Re: [GENERAL] How To Install Extension Via Script File?

[Don Parris]

On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent robjsarg...@gmail.com wrote:

  On 08/08/2013 03:13 PM, Don Parris wrote:

Hi all,

  I have a database that uses the ltree extension.  I typically create a
 new database like so (as a normal user), using my script file:

 CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

  And then su to postgres, login and install the ltree extension on mydb.
 Then I logout of my psql instance and re-run the script (as a normal user)
 to create the tables  views on mydb.  I comment out the table/view
 creation portion until I finish the first couple steps, and then uncomment
 the tables and views on the 2nd run.  Otherwise the script will fail
 because the ltree extension has to be installed as a superuser.

 I want a script something like:
  CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';
  \c mydb
  CREATE EXTENSION ltree;
  CREATE TABLE mytable(myfields);
  rinse, repeat for additional tables and views.

  SNIP

   Have you tried adding the extension to template1.  I find that works
 nicely as it means CREATE DATABASE dbname gets the extension.  That
 said, I'm wondering if you're actually having trouble accessing the
 extension subdirectory.  Perhaps the server is running as different user
 than the owner of the extensions?


Thanks Rob,
If I do that, and then create DB, as I do, using template0 ENCODING
UTF8, the extension does not appear to be installed on the new database.
At least, when I tried that before, it did not appear to work.  I had to
install the extension on the database anyway.  I have not had time to delve
into how to resolve that - hasn't really been all that important until now.

Thanks!
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] How To Install Extension Via Script File?

[Don Parris]

On Thu, Aug 8, 2013 at 6:30 PM, John R Pierce pie...@hogranch.com wrote:

 On 8/8/2013 2:13 PM, Don Parris wrote:

 I thought I could add the commands and run the create script by doing:
 sudo -u postgres psql -U user -W -d mydb --file=/home/user/dev/mydb_**
 create.sql

 I thought that, running my script as the superuser, it would have the
 privileges necessary to install the extension, but I still got a
 permission denied error.


 can the postgres user access /home/user/dev ?

 thats aside from the rest of potentially wrong stuff the other guys
 mentioned.


Oops!  That *could* create havoc, couldn't it?Still, should be easy
enough to rectify.

[GENERAL] Connecting Multiple LibreOffice Base Users to a Remote Postgres Database

[Don Parris]

Hi all,

I have been posting on my blog about getting LibreOffice Base and
PostgreSQL working together.  My latest actually focuses on getting
multiple users connecting to a single, remote, stand-alone PostgreSQL
server.  You can set this up fairly quickly and - in my case, anyway - with
no programming involved.

http://dcparris.net/2013/08/04/distributing-your-libreoffice-postgresql-database-across-systems-and-users-the-basics/

Of course, if you want to enforce business rules and security, you'll need
to employ server-side tools and programming, as well as possibly using Base
macros or Python on the client side.  I am not yet certain about how to
implement transactions, so that (following the project below), one can
complete a form and press a Submit button to make the whole transaction
fire at once.

My previous post is not so much of a tutorial, but more of an opportunity
to showcase a small project using LibreOffice Base as a front-end to
PostgreSQL.

http://dcparris.net/2013/07/22/ill-call-it-ldinero/

In any case, I hope these will be of some use to others here.  Please enjoy
and let me know if you need any clarification of any of my points.

Regards,
Don

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

[GENERAL] LibreOffice Base and PostgreSQL Transactions

[Don Parris]

Hi all,

I have a 4-table DB in PostgreSQL to which I connect using LibreOffice
Base.  I have a form with 2 subforms in place that allows me to:
Select an existing entity (from the ENTITY table)
Add a new financial transaction (to the TRANSREC table)
Add new line items for each transaction (to the TRANSDETAILS table).

I would like to take advantage of PostGRESQL's underlying transaction
(start, rollback, commit, etc.) capability to ensure that a given
transaction and its
line items are entered as one single transaction - or allow me to rollback
when encountering a glitch.  I just don't know how to implement that using
Base as a front-end (or if it's possible).

Maybe I really need something like macros and BASIC or Python to make such
a thing work?  If anyone knows a good tutorial on this subject, I can
certainly read - just haven't really found anything yet.

Many thanks in advance,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] LibreOffice Base and PostgreSQL Transactions

[Don Parris]

On Thu, Jul 18, 2013 at 5:01 PM, Joshua D. Drake j...@commandprompt.comwrote:


 On 07/18/2013 01:44 PM, Don Parris wrote:

  Maybe I really need something like macros and BASIC or Python to make such
 a thing work?  If anyone knows a good tutorial on this subject, I can
 certainly read - just haven't really found anything yet.

 Many thanks in advance,


 Hello,

 Maybe this will help:

 http://forum.openoffice.org/**en/forum/viewtopic.php?f=45t=**1426http://forum.openoffice.org/en/forum/viewtopic.php?f=45t=1426

 JD


Thanks Josh, that helps a little.  I get the impression from Drew's
discussion that it is possible to choose between creating stored procedures
in LO Basic and the PGSQL native procedures.  Is that correct?  He gives no
examples of how to use the MySQL triggers/procedures, so I am not sure how
to ensure that, as I enter my data in the form in Base, it gets held
until I am ready to hit submit.

I know very little of LO/OO Basic - or of stored procedures, for that
matter.  So it looks like I've got a bit of reading to do.

[GENERAL] PostgreSQL Presentation at SELF 2013

[Don Parris]

Hi all,

I am a member of the Charlotte Linux User Group and did a presentation on
PostgreSQL at the SouthEast LinuxFest this past weekend.  Our LUG table was
right next to the PostgreSQL table this year, and I am guessing some of you
will find the presentation of some interest.  My discussion focused on
using the ltree module instead of adjacency lists, nested sets and path
enumeration to create and run queries against hierarchical category trees.

Here is a link to my slides (*.odp) on the ltree module:
http://dcparris.net/2013/06/11/self-2013-ltree-slides/

and to my general take on the SouthEast LinuxFest.
http://dcparris.net/2013/06/11/southeast-linuxfest-what-you-missed/

Anyway, here's hoping you'll enjoy this and find it useful as the few
attendees who sat in did.  I hope it helps in some small way.

Regards,
Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] Tutorial On Connecting LibreOffice to PostgreSQL Available

[Don Parris]

You're welcome!  Heaven knows, I ask plenty of questions - it's good to be
able to offer an answer now and again.  :-)

Don

On Mon, Jul 9, 2012 at 6:04 PM, Willy-Bas Loos willy...@gmail.com wrote:

 thx for sharing!


 On Fri, Jul 6, 2012 at 9:13 PM, Don Parris parri...@gmail.com wrote:

 Hi all,

 I believe this may be pertinent here.  Last year I wrote a tutorial on
 connecting LibreOffice to the powerful PostgreSQL database server. Now
 there is an updated driver that allows read-write access. So I've updated
 my tutorial, complete with screenshots this time.  The actual connection
 string to use (the URL) is not at all obvious to most people, so the
 tutorial reveals the magic words, as well as addressing one or two other
 options that may not be obvious to less experienced users.


 http://dcparris.net/2012/07/06/connecting-libreoffice-to-postgresql-natively/


 Enjoy!



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

[GENERAL] Tutorial On Connecting LibreOffice to PostgreSQL Available

[Don Parris]

Hi all,

I believe this may be pertinent here.  Last year I wrote a tutorial on
connecting LibreOffice to the powerful PostgreSQL database server. Now
there is an updated driver that allows read-write access. So I've updated
my tutorial, complete with screenshots this time.  The actual connection
string to use (the URL) is not at all obvious to most people, so the
tutorial reveals the magic words, as well as addressing one or two other
options that may not be obvious to less experienced users.

http://dcparris.net/2012/07/06/connecting-libreoffice-to-postgresql-natively/


Enjoy!
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE

Re: [GENERAL] [NOVICE] Question About Aggregate Functions

[Don Parris]

On 9/13/06, Brandon Aiken [EMAIL PROTECTED] wrote:















Ah, I did not know what was in your
fields, so I did not assume they were Boolean values. It looked to me
like you were trying to use IS TRUE to substitute for the lack of a GROUP BY,
so I didn't know what to do.That was in the first paragraph of my OP.  How do I create a query that (1) evaluates each boolean field for
TRUE/FALSE and (2) counts the number of rows where each field is TRUE? Maybe you just hadn't had your first cup of coffee? ;-) Seriously, though, I really do appreciate your help.
Yes, count() will include all non-NULL
values. Sorry if I sounded unclear there. 



If you do typecasting the value zero is false
(and non-zero is true). NULL in an _expression_ always returns NULL, and
many programs will interpret that result as false. So I'm not sure
of what results you might get with a Boolean test against a non-Boolean field,
especially if it's an integer field.



postgres=# select 0::boolean = FALSE;

?column?

--

t

(1 row)



You should just be able to take the
previous query and add in your WHERE clauses:



SELECT count(t1.fielda), count(t2.fielda),
count(t2.fieldb), AVG(t2.fieldc)

FROM t1 JOIN t2 ON t1.item_id = t2.item_id

WHERE t1.fielda = TRUE AND t2.fielda =
TRUE AND t2.fieldb = TRUE

GROUP BY NULL;



Now, the INNER JOIN you're using is
only selecting fields where both t1.item_id and t2.item_id exist and the respective
fields are TRUE. That is, it's only going to run the count and
average functions against the results of this query:

SELECT *

FROM t1 JOIN t2 ON ON t1.item_id =
t2.item_id

WHERE t1.fielda = TRUE AND t2.fielda =
TRUE AND t2.fieldb = TRUE;



If that's what you want, that's
great. Can I use OR instead of AND here?
However, you might want a count of each
field where that field is TRUE. In that case, I would use either
temporary tables, compound queries and derived tables, or multiple simple
queries.
It's also possible that you might
want a count of fields where t1.item_id and t2.item_id exist, but where only
each respective field is TRUE. That is, you want a count of t1.fielda
where it is TRUE no matter what t2.fielda and t2.fieldb are as long as
t1.item_id matches t2.item_id. In that case you have to do even more
joins, and that could take a fair bit of time especially if you haven't
indexed your item_id fields. Well this sounds more like what I want. Given t2.fielda, t2.fieldb, t2.fieldc, any one (or all three) could be true, but frequently at least one of the fields is false. Initially, all of the fields might be unknown (thus NULL) for a given item until I am able to investigate the items to determine TRUE/FALSE. I frequently have items that are inactive, and thus unable to determine any of attributes in t2.
My end result needs to be a count of all the values in each field where the value is TRUE, as opposed to FALSE or NULL.


You really have to look at your result
sets. Sometimes it is better to run multiple simple queries instead of
one big complex query to be sure you're getting the data you want and the
query executes in a reasonable amount of time.



Also, consider that NULL values are
generally considered bad to purposefully enter. Logically, It would be
better to create one table for each field and then create a record for each
item_id as you need it so you never have NULLs. The problem with that is
one of performance if you end up doing large number of JOINs. In that
case, it might be better to use integers instead of Boolean fields, since you
have three explicit states of TRUE, FALSE, and NOT YET DETERMINED.Regarding the NULL Values:I have been thinking that I might want to leave my NULLs as they are. However, I will be concentrating mostly on the items that are active. Inactive items are only counted as part of the total number of items. Their attributes are a moot point, but technically FALSE. (If they are inactive, the attributes are no longer TRUE in any case.)
I am counting only those fields that I know (through verification) to be TRUE. I can use FALSE where the actual value is not known, and just change the attribute to TRUE when I discover that to be the case. I just need to be sure in my counts that I note the fact that FALSE values include the unverified values (meaning some of those might actually be true). Does that sound fairly logical to you?
Regarding the Table Layout:The columns in t2 are ordered, essentially according to the category of attributes involved. I had thought about using multiple tables, one for each category of attributes. However, I chose a monolithic table for the attributes to represent a single survey of each item. Each item might be surveyed again in the future to determine any changes, which would introduce a new version of the current table.
I'll tinker around with the queries a bit, and see what I come up with.Thanks for the input.