tapis-project/paas

The PgREST service provides a friendly RESTful API to a managed Postgres database with management tools for tables, views, and roles.

paas's Issues

Feature request: Complex view support

I like the straightforward approach to setting up views in the current pgrest, but oftentimes views are based on some pretty complex SQL queries (including joins, conditionals, aggregations, etc) that might be hard to implement using a declarative form like we do for table creation, etc. I would like to propose allowing the user to provide something approximating raw SQL for the view definition. This would be an alternative to the simpler (and easier-to-use) form that is supported now.

Deployer QOL fixes.

New Github deployment actions and change startup fails so they don't cause issue. Previously role creation was breaking when attempting to create roles on other sites. Nginx routing is also working again, thanks to Smruti.

Feature request: Bulk row creation

To optimize larger data load operations, I am suggesting development of a feature allowing creation of multiple rows in a single action.

The current API supports POST of a JSON doc structured as below, returning the newly created row as a TapisResult.

{
    "data": {
      "name": "example-widget",
      "widget_type": "gear",
      "count": 0,
      "is_private": false
    }
}

Maybe it would work to support posting a structure like this to load multiple rows at once:

{
	"data": [{
		"name": "example-widget",
		"widget_type": "gear",
		"count": 0,
		"is_private": false
	}, {
		"name": "example-widget-2",
		"widget_type": "gear",
		"count": 1,
		"is_private": false
	}, {
		"name": "example-widget-3",
		"widget_type": "gear",
		"count": 4,
		"is_private": false
	}]
}

The response from the service would be the list of created rows (as TapisResults).

To make this bulk insert an atomic operation, the entire set of row adds could be wrapped in a single transaction, being rolled back if any of the row creations fail, with the most relevant error being returned as a message from the service.

Extra forward slashes not resolving properly.

Raised by Joshua Urrutia.
PgREST seems to not resolve extra forward slashes in the URL properly. RFC 2396 hints that it should resolve properly.

https://tacc.develop.tapis.io///v3///pgrest

Probably an issue with Django or pattern recognition. Error below.

 'metadata': {},
 'result': None,
 'status': 'error',
 'version': 'dev'}

Need more testing for recently added features.

Add docstrings to functions and write some more comments.

Support for limit and offset in list_in_collection

According to the OpenAPI spec (and based on functional tests), it seems like limit and offset support are missing from list_in_collection.

tapis3 pg tables list organization
+---------------------------------------+-------+-----------------+----------------------+-------+
| description                           | name  | organization_id | url                  | _pkid |
+---------------------------------------+-------+-----------------+----------------------+-------+
| Data Coordinating Center              | TACC  |               1 | tacc.utexas.edu      |     1 |
| University of Illinois at Chicago     | UIC   |               2 | uic.edu              |     2 |
| Rush University Medical Center        | Rush  |               3 | rush.edu             |     3 |
| Institute for Translational Medicine  | ITM   |               4 | chicagoitm.org       |     4 |
| University of Chicago Medical Center  | UCMC  |               5 | uchicagomedicine.org |     5 |
| NorthShore                            | NS    |               6 | northshore.org       |     6 |
| University of Michigan                | UMICH |               7 | umich.edu            |     7 |
| University of Iowa                    | UIOWA |               8 | uiowa.edu            |     8 |
| Pacific Northwest National Laboratory | PNNL  |               9 | pnnl.gov             |     9 |
| University of California San Diego    | UCSD  |              10 | ucsd.edu             |    10 |
+---------------------------------------+-------+-----------------+----------------------+-------+

There are > 10 records in this collection. Sending a limit kwarg to list_in_collection does not change the response.

Add a comment field for each column

I propose adding an optional short (say 255 char) comment field for each column. This would make PgREST table specs a little more self-documenting. This would make it easy to generate human-usable forms and renderings around PgREST.

Feature request: "where" query support when getting rows from a view

Views act as virtual tables. Tables can be queried using a "where" parameter. Thus, views should also be queryable using a where parameter.

Change view implementation for non privileged users.

Currently PGREST_READ is required for views endpoints, meaning that any user can actually see anything else as well.

• Get rid of PGREST_READ permission on views.
• Create new unprivileged user so non-PgREST users can't access.
• Ensure views still work with new role.

Clarify whether 'CASCADE' and 'SET NULL' are case-sensitive

it isn't clear whether the values for on_delete in a foreign key constraint must be uppercased. I suggest either clarifying this or (my preference) making them case-insensitive.

Views are not deleted from pgrest_managedviews when view is deleted

When a view is deleted administratively (or by cascading processes in postgresql), the corresponding row in the pgrest_managedviews table is not deleted. This prevents a view with the same name from being created in the future. The current workaround is to manually delete the rows using a DBMS client.

Update PgREST spec and add changelog for 1.1

Feature request: Table schema updates

It can be a lot of work to dump, drop, create, and load tables in order to make a trivial update to a schema, especially when there are tables linked by foreign key constraints. Please consider providing support for some degree of dynamic table schema updates.

`raw_sql` view creation still requires `from_table` parameter.

No longer needed, only kept to ensure table exists, but complex from_tables can be used and it isn't very necessary.

Fixed issues in in table row update and deployment changes

Caught by Joshua Urrutia

Convert PgREST to use new Tapipy with plugin support.

Replacing pycommon for a centralized source for common code.

Feature request: support "order by"

In addition to where, limit, and offset, it would be helpful to support ordering by field name, allowing for specification of ascending or descending sort.

Improvement: Don't enforce "limit" by default on queries

If I am doing a query on a table, I expect to get all the rows back if I do not specify a limit. This is equivalent to FROM table SELECT * WHERE %CRITERA; In the current pgrest implementation, I get the first 10 records back, which was a bit surprising. So, I either have to arbitrarily provide a large value for 'limit or iterate through the responses, which adds at minimum, one additional API call just to do a select-type operation.

Feature request: Allow views to reference other views

On the way to suggesting issue #34 I discovered that pgrest views can't reference other views. I'd like to suggest we change that.

Clarify whether 'on_update' is supported for foreign key constraints

PostgreSQL lets us define behavior for deletes but also updates. Can we clarify whether on_update is supported in PgREST?

Revamped CI with Github actions

Clarify whether UPDATETIME and CREATETIME are case sensitive

It isn't clear from the docs whether UPDATETIME and CREATETIME are case sensitive. If they are, it should be stated, but I would suggest instead making them case-insensitive and defaulting to the lower case form in the docs.

Change: Serial data type doesn't imply unique.

Serial columns can be overwritten and then the SERIAL type will overwrite that causing issues for PGREST.

Talked to Matt, will change SERIAL to "start" at integer 1000 instead of 0 to more than likely get rid of any collisions. Will also enforce UNIQUE.

Deploy 1.1.0 on Tapis schedule

Ready, 1.0.3 is pre-release.

Test Issue v5

testing comment

Support for table-level constraints

I am requesting support for table-level constraints. Specifically, and to start with, we need multi-column unique constraints for the A2CPS project. I propose the following extension to the PgREST table definition:

{
	"table_name": "UserProfile",
	"root_url": "user-profile",
	"constraints": {
		"unique": {
			"first_last": ["first_name", "last_name"]
		}
	},
	"columns": {
		"user_profile_id": {
			"data_type": "serial",
			"primary_key": true
		},
		"username": {
			"unique": true,
			"data_type": "varchar",
			"char_len": 255
		},
		"first_name": {
			"null": true,
			"data_type": "varchar",
			"char_len": 255
		},
		"last_name": {
			"null": true,
			"data_type": "varchar",
			"char_len": 255
		}
	}
}

In this trivial example, all User Profile records would be forced have a unique combination of first and last name. On the backend, this would map roughly to:

CREATE UNIQUE INDEX UserProfile_first_last ON UserProfile(first_name, last_name)

Unique two variable constraint not working currently.

Add a comment field for each table

I propose adding an optional short (say 255 char) comment field for each table. This would make PgREST table specs a little more self-documenting. This would make it easy to generate human-usable forms and renderings around PgREST.

Constraints of the same name are currently colliding during table creation.

Specifically declaring a multi-column unique constraint.
ex. 'constraints': {'unique': {'uniqueConstraintName': ['column1', 'column2']}}
A user cannot use the name uniqueConstraintName more than once.

I believe this is the expected outcome, if true at least have a better error message, if not, fix the sql.

Table names and view names can collide. Enforce uniqueness.

Materialized View support alongside regular views.

Pat Scherer referenced wanting materialized view support in PgREST for some future work.

Consider changing property name 'FK' to 'foreign_key'

It's not intuitive to me what the property FK is in a table definition. It's (at least in the docs) uppercase and is also an abbreviation unlike the other table properties. I suggest changing the property name to foreign_key.

Feature request: floating point number support

Please consider adding support for one or more postgresql floating point number types... numeric, decimal, real, double precision. It's not straightforward at present to work with these in PgREST.

Dangling view record can't be deleted.

There's an edge case where Django believes a view exists, but it doesn't. This results in it being impossible for a user to delete a view.

message: Failed to drop view data_events_base from the ManageViews table: Error dropping view a2cpsdev.data_events_base: Error accessing database: view "data_events_base" does not exist