Обсуждение: create tablespace - cannot run inside a transaction block
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,
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/';
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.
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
Вложения
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
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.
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.
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