Обсуждение: create tablespace - cannot run inside a transaction block

Поиск
Список
Период
Сортировка

create tablespace - cannot run inside a transaction block

От
Pepe TD Vo
Дата:
Good morning Experts,

I'm so sorry for asking a stupid question in postgres again.  I am working on AWS server/Postgresql.  When I execute a query for creaing the database and/or from the pgAdmin wizzard, I get an error, " CREATE database cannot run inside a transaction block"
Finally, I created a database and when I try to create tablespace, I get the same error that cannot run inside a transaction block.   I used to create tablespaces fine before migrate to Postgres 11.x,  

Inline image

CREATE TABLESPACE pkihtts_data
  OWNER "PSmasteruser"
  LOCATION '/rdsdbdata/db/base/tablespace/pkihtts_data';

or 
CREATE TABLESPACE pkihtts_data
  OWNER "PSmasteruser"
  LOCATION '/rdsdbdata/db/base/tablespace/';

or
CREATE TABLESPACE pkihtts_data
  OWNER "PSmasteruser"
  LOCATION '/rdsdbdata/';

Inline image

none of them work.  
Normally, in Oracle we need to create database, tablespace then username/schema and tables, objects, etc...

is the procedure as same as in Postgres?  I see the login and schema are totally different in Postgres.

any input would appreciated.

thank you,




Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
Вложения

Re: create tablespace - cannot run inside a transaction block

От
Laurenz Albe
Дата:
On Wed, 2019-09-25 at 14:50 +0000, Pepe TD Vo wrote:
> Normally, in Oracle we need to create database, tablespace then
> username/schema and tables, objects, etc...
> 
> is the procedure as same as in Postgres?  I see the login and schema
> are totally different in Postgres.

No, normally you don't create tablespaces in PostgreSQL.
They are a few use cases for them, but not many.

There is nothing wrong with creating a schema with the same name
as the user in PostgreSQL, as long as you make sure that the
user owns the schema.

Then you have emulated Oracle's behaviour somewhat.

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Better I/O throughput? (was Re: create tablespace - cannot run insidea transaction block)

От
Ron
Дата:
On 9/25/19 2:16 PM, Laurenz Albe wrote:
> On Wed, 2019-09-25 at 14:50 +0000, Pepe TD Vo wrote:
>> Normally, in Oracle we need to create database, tablespace then
>> username/schema and tables, objects, etc...
>>
>> is the procedure as same as in Postgres?  I see the login and schema
>> are totally different in Postgres.
> No, normally you don't create tablespaces in PostgreSQL.
> They are a few use cases for them, but not many.

Do I/O requests in the Linux kernel get "backlogged" when they all hit the 
same device?  Or would you get better throughput (or less latency) by 
spreading the load across multiple devices?

(A long-running synchronous IO request seems it would block everything 
behind it, whereas objects in tablespaces on different devices could still 
be queried.)

-- 
Angular momentum makes the world go 'round.

Re: Better I/O throughput? (was Re: create tablespace - cannot runinside a transaction block)

От
Avin Kavish
Дата:
In reply to the original question being raised about an RDS instance, afaik, there's no need to do tablespaces on RDS as IOPS is provisioned as requested, the actual hardware implementation is abstracted away and irrelevant.

On Thu., 26 Sep. 2019, 1:10 am Ron, <ronljohnsonjr@gmail.com> wrote:
On 9/25/19 2:16 PM, Laurenz Albe wrote:
> On Wed, 2019-09-25 at 14:50 +0000, Pepe TD Vo wrote:
>> Normally, in Oracle we need to create database, tablespace then
>> username/schema and tables, objects, etc...
>>
>> is the procedure as same as in Postgres?  I see the login and schema
>> are totally different in Postgres.
> No, normally you don't create tablespaces in PostgreSQL.
> They are a few use cases for them, but not many.

Do I/O requests in the Linux kernel get "backlogged" when they all hit the
same device?  Or would you get better throughput (or less latency) by
spreading the load across multiple devices?

(A long-running synchronous IO request seems it would block everything
behind it, whereas objects in tablespaces on different devices could still
be queried.)

--
Angular momentum makes the world go 'round.


Re: Better I/O throughput? (was Re: create tablespace - cannot runinside a transaction block)

От
Laurenz Albe
Дата:
On Wed, 2019-09-25 at 14:40 -0500, Ron wrote:
> > No, normally you don't create tablespaces in PostgreSQL.
> > They are a few use cases for them, but not many.
> 
> Do I/O requests in the Linux kernel get "backlogged" when they all
> hit the 
> same device?  Or would you get better throughput (or less latency)
> by 
> spreading the load across multiple devices?

Sure, spreading I/O across multiple devices is one use case for
tablespaces.  But usually this is easier to do by striping a
logical volume across several physical volumes at a lower level.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com